Remarks

39   public DataSet GetLoad(string sortfield)
40   {
41
42    System.Data.OleDb.OleDbConnection conn = GetConnection();
43    DataSet ds = new DataSet();
44
45    try
46    {
47
48     // Dim sql As String = "SELECT ID data1 as [Firstname],data2 as [Lastname],data3 as [20],data4 as [EG],data5 as [1st-10%],data6 as [50pt],data7 as [EG1],data8 as [2nd-10%],data9 as [50pts],data10 as [EG2],data11 as [20%],data12 as [100pt],data13 as [EG3],data14 as [30%],data15 as [100],data16 as [EG4],data17 as [2nd-30%],data18 as [PrelimGrade],data19 as [Remarks]FROM Table1 order by " + sortfield
49     string sql = "SELECT STUDENTID,data1 AS Firstname, data2 AS Lastname," + "data3 AS 20, data4 AS EG, data5 AS [1st-10%], data6 AS 50pt," + "data7 AS EG1, data8 AS [2nd-10%], data9 AS 50pts, data10 AS EG2," + "data11 AS [20%], data12 AS 100pt, data13 AS EG3, data14 AS [30%]," + "data15 AS 100, data16 AS EG4, data17 AS [2nd-30%], data18 AS PrelimGrade," + "data19 AS Remarks FROM Table1 ORDER BY ID";
50
51     System.Data.OleDb.OleDbDataAdapter da = new System.Data.OleDb.OleDbDataAdapter(sql, conn);
52     try
53     {
54      da.Fill(ds, "grade1");
55     }
56     finally
57     {
58      da.Dispose();
59     }
60     return ds;
61    }
62    finally
63    {
64     conn.Close();
65     conn.Dispose();
66    }
67   }

68   public void RefreshDGV()
69   {
70
71    System.Data.OleDb.OleDbConnection conn = GetConnection();
72    string sql = default(string);
73    sql = "SELECT STUDENTID,data1 AS Firstname, data2 AS Lastname," + "data3 AS 20, data4 AS EG, data5 AS [1st-10%], data6 AS 50pt," + "data7 AS EG1, data8 AS [2nd-10%], data9 AS 50pts, data10 AS EG2," + "data11 AS [20%], data12 AS 100pt, data13 AS EG3, data14 AS [30%]," + "data15 AS 100, data16 AS EG4, data17 AS [2nd-30%], data18 AS PrelimGrade," + "data19 AS Remarks FROM Table1 ORDER BY ID";
74
75    System.Data.OleDb.OleDbDataAdapter da = new System.Data.OleDb.OleDbDataAdapter(sql, conn);
76    DataTable dt = new DataTable("grade1");
77    da.Fill(dt);
78    Form1.Default.DataGridView1.DataSource = dt;
79   }

86         private void btnsave_Click(object sender, EventArgs e)
87         {
88
89             foreach(Control obj in pnl_stockmaster.Controls)
90             {
91                 if(obj is TextBox)
92                 {
93                     if(obj.Text == "")
94                     {
95                         MessageBox.Show("Action connot be perform. All fields are required to be fill up.", "Invalid", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
96                         return;
97                     }
98                 }
99             }
100
101
102
103             sql = "INSERT INTO `tblstock_in_out` (`ITEMID`, `QTY`,`TRANSACTIONDATE`, `TOTALPRICE`, `REMARKS`)" +
104               " VALUES ('" + txtitemid.Text + "','" + txtqty.Text + "','" + DateTime.Now.ToString("yyyy-MM-dd") + "','" + txtprice.Text +
105               "','StockIn')";
106            config.Execute_Query(sql);
107
108
109             sql = "SELECT ITEMID FROM tblitems WHERE ITEMID='" + txtitemid.Text + "'";
110             config.singleResult(sql);
111             if(config.dt.Rows.Count > 0)
112             {
113                 sql = "UPDATE tblitems SET qty =qty + '" + txtqty.Text + "' WHERE ITEMID ='" + txtitemid.Text + "'";
114                 config.Execute_Query(sql);
115             }
116             else
117             {
118                 sql = "insert into tblitems (ITEMID,`NAME`, `DESCRIPTION`, `TYPE`, `PRICE`, `QTY`,UNIT)" +
119                    "VALUES ('" + txtitemid.Text + "','" + txtname.Text + "','" + txtdescription.Text + "','" + cbotype.Text
120                    + "','" + txtprice.Text + "','" + txtqty.Text + "','" + cbounit.Text + "' )";
121                 config.Execute_CUD(sql, "No data saved.", "Data has been saved in the database.");
122
123                 config.update_Autonumber(cbotype.Text);
124             }
125
126             btnnew_Click(sender, e);
127
128         }

21         private void frmListStockout_Load(object sender, EventArgs e)
22         {
23             sql = "SELECT `TRANSACTIONNUMBER` as ID,concat(`FIRSTNAME`, `LASTNAME`) as 'CUSTOMER',`NAME` as 'ITEMNAME', `DESCRIPTION`, `PRICE`, `TRANSACTIONDATE` as DATE, o.`QTY`, `TOTALPRICE` FROM `tblperson` p,`tblstock_in_out` o ,`tblitems` i WHERE REMARKS='StockOut' and i.`ITEMID`=o.`ITEMID` and p.`SUPLIERCUSTOMERID`=o.`SUPLIERCUSTOMERID` and p.TYPE = 'Customer'" +
24               " AND (NAME like '%" + txtsearch.Text + "%' OR TRANSACTIONNUMBER like '%" + txtsearch.Text + "%')";
25             config.Load_DTG(sql, dtglist);
26         }

62         private void btnListStockin_Click(object sender, EventArgs e)
63         {
64
65             switch (cbooption.Text)
66             {
67
68                 case "Daily Report":
69                     sql = "SELECT i.`ITEMID`, `NAME`, `DESCRIPTION`, `TYPE`, `PRICE`,so.`QTY`, `UNIT`,TRANSACTIONDATE FROM `tblitems` i ,`tblstock_in_out` so WHERE i.`ITEMID`=so.`ITEMID` AND `REMARKS`='StockIn' AND DATE(`TRANSACTIONDATE`)=CURDATE()";
70                     reports(sql, "itemlist");
71                     break;
72
73                 case "Weekly Report":
74                     sql = "SELECT i.`ITEMID`, `NAME`, `DESCRIPTION`, `TYPE`, `PRICE`,so.`QTY`, `UNIT`,TRANSACTIONDATE FROM `tblitems` i ,`tblstock_in_out` so WHERE i.`ITEMID`=so.`ITEMID` AND `REMARKS`='StockIn' AND WEEKDAY(`TRANSACTIONDATE`) >=0 AND WEEKDAY(`TRANSACTIONDATE`) <=4";
75                     reports(sql, "itemlist");
76                     break;
77                 case "Monthly Report":
78                     sql = "SELECT i.`ITEMID`, `NAME`, `DESCRIPTION`, `TYPE`, `PRICE`,so.`QTY`, `UNIT`,TRANSACTIONDATE FROM `tblitems` i ,`tblstock_in_out` so WHERE i.`ITEMID`=so.`ITEMID` AND `REMARKS`='StockIn' AND MONTH(`TRANSACTIONDATE`)=MONTH(CURDATE())";
79                     reports(sql, "itemlist");
80                     break;
81
82             }
83         }

85         private void btnStockOut_Click(object sender, EventArgs e)
86         {
87             switch (cbooption.Text)
88             {
89                 case "Daily Report":
90                     sql = "SELECT `TRANSACTIONNUMBER` , concat(`FIRSTNAME`,' ', `LASTNAME`) as 'Customer', `NAME` AS 'Item', io.`QTY` , `TOTALPRICE` , `TRANSACTIONDATE` " +
91                         " FROM `tblitems` i, `tblstock_in_out` io,`tblperson` p" +
92                         " WHERE i.`ITEMID` = io.`ITEMID` AND io.`SUPLIERCUSTOMERID`=p.`SUPLIERCUSTOMERID` AND REMARKS ='StockOut' AND DATE(`TRANSACTIONDATE`)=CURDATE()";
93                     reports(sql, "soldList");
94                     break;
95
96                 case "Weekly Report":
97                     sql = "SELECT `TRANSACTIONNUMBER` , concat(`FIRSTNAME`,' ', `LASTNAME`) as 'Customer', `NAME` AS 'Item', io.`QTY` , `TOTALPRICE` , `TRANSACTIONDATE` " +
98                         " FROM `tblitems` i, `tblstock_in_out` io,`tblperson` p" +
99                         " WHERE i.`ITEMID` = io.`ITEMID` AND io.`SUPLIERCUSTOMERID`=p.`SUPLIERCUSTOMERID` AND REMARKS ='StockOut' AND WEEKDAY(`TRANSACTIONDATE`) >=0 AND WEEKDAY(`TRANSACTIONDATE`) <=4";
100                     reports(sql, "soldList");
101                     break;
102                 case "Monthly Report":
103                     sql = "SELECT `TRANSACTIONNUMBER` , concat(`FIRSTNAME`,' ', `LASTNAME`) as 'Customer', `NAME` AS 'Item', io.`QTY` , `TOTALPRICE` , `TRANSACTIONDATE` " +
104                         " FROM `tblitems` i, `tblstock_in_out` io,`tblperson` p" +
105                         " WHERE i.`ITEMID` = io.`ITEMID` AND io.`SUPLIERCUSTOMERID`=p.`SUPLIERCUSTOMERID` AND REMARKS ='StockOut' AND MONTH(`TRANSACTIONDATE`)=MONTH(CURDATE())";
106                     reports(sql, "soldList");
107                     break;
108
109             }
110         }

133         private void button1_Click(object sender, EventArgs e)
134         {
135             sql = "SELECT `NAME` , `DESCRIPTION` , `TYPE` , i.`QTY` AS 'Stock-In', `UNIT` , o.`QTY` AS 'Stock-Out', `TRANSACTIONDATE` , `REMARKS` " +
136                   "FROM `tblitems` i" +
137                   " LEFT JOIN `tblstock_in_out` o ON i.`ITEMID` = o.`ITEMID` " +
138                   " AND REMARKS = 'StockOut' and TRANSACTIONDATE between '" + dptfrom.Text + "' and '" + dtpto.Text + "'";
139             reports(sql, "inventory");
140         }

25         private void frmReturn_Load(object sender, EventArgs e)
26         {
27             sql = "SELECT `TRANSACTIONNUMBER` FROM `tblstock_in_out` WHERE `REMARKS`='Stockout'";
28             config.autocomplete(sql, txttransactionid);
29         }

130         private void btnCus_save_Click(object sender, EventArgs e)
131         {
132             string stockoutID;
133
134             config.singleResult("SELECT concat(STRT,END) FROM tblautonumber WHERE ID = 5");
135             stockoutID = config.dt.Rows[0].Field(0);
136
137             if (txt_cusid.Text == "")
138             {
139                 MessageBox.Show("There are empty fields left that must be fill up!", "Invalid", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
140                 return;
141             }
142             if(dtCus_addedlist.RowCount == 0)
143             {
144                 MessageBox.Show("Cart is empty!", "Invalid", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
145                 return;
146             }
147
148             sql = "SELECT ITEMID,`QTY` FROM `tblitems`";
149             config.singleResult(sql);
150
151             foreach(DataRow row in config.dt.Rows)
152             {
153                 for(int i = 0;i < dtCus_addedlist.Rows.Count; i++)
154                 {
155                     if (dtCus_addedlist.Rows[i].Cells[0].Value.ToString() == row.Field(0))
156                     {
157                         if(int.Parse( dtCus_addedlist.Rows[i].Cells[4].Value.ToString()) > row.Field(1))
158                         {
159                             MessageBox.Show("The Quantity of the item ( " + dtCus_addedlist.Rows[i].Cells[1].Value.ToString() + " ) is greater than the available quantity of it.", "Invalid", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
160                             return;
161                         }
162                     }
163                     if (dtCus_addedlist.Rows[i].Cells[4].Value.ToString() == "")
164                     {
165                         MessageBox.Show("Set your purpose.", "Invalid", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
166                         return;
167                     }
168                 }
169             }
170
171             foreach(DataGridViewRow r in dtCus_addedlist.Rows)
172             {
173                 sql = "INSERT INTO `tblstock_in_out` ( `TRANSACTIONNUMBER`, `ITEMID`, `TRANSACTIONDATE`, `QTY`, `TOTALPRICE`, `SUPLIERCUSTOMERID`,REMARKS)" +
174                 " VALUES ('" + stockoutID + "','" + r.Cells[0].Value + "','" + DateTime.Now.ToString("yyyy-MM-dd") + "','" + r.Cells[4].Value +
175                 "','" + r.Cells[5].Value + "','" + txt_cusid.Text + "','StockOut')";
176                 config.Execute_Query(sql);
177
178                 sql = "UPDATE `tblitems` SET `QTY`= QTY - '" + r.Cells[4].Value + "' WHERE ITEMID='" + r.Cells[0].Value + "'";
179                 config.Execute_Query(sql);
180             }
181
182
183             sql = "INSERT INTO `tbltransaction` (`TRANSACTIONNUMBER`, `TRANSACTIONDATE`, `TYPE`, `SUPLIERCUSTOMERID`)" +
184                    " VALUES ('" + stockoutID + "','" + DateTime.Now.ToString("yyyy-MM-dd") + "','StockOut','" + txt_cusid.Text + "')";
185             config.Execute_Query(sql);
186
187             // '-----------------------------------------------update autonumber
188             config.Execute_Query("UPDATE tblautonumber SET END= END + INCREMENT WHERE ID = 5");
189
190             // '------------------------------------------------------------
191             MessageBox.Show("Item(s) has been save in the database.");
192             // '------------------------------------------------------------clearing
193             funct.clearTxt(Panel1);
194             dtCus_addedlist.Rows.Clear();
195
196             frmStockOut_Load(sender, e);
197         }

93         private void Button1_Click(object sender, EventArgs e)
94         {
95             try
96             {
97             GroupBox3.Visible = true;
98             con = new SqlConnection(cs.DBConn);
99             con.Open();
100             cmd = new SqlCommand("SELECT RTRIM(invoiceNo) as [Order No],RTRIM(InvoiceDate) as [Order Date],RTRIM(Invoice_Info.CustomerID) as [Customer ID],RTRIM(CustomerName) as [Customer Name],RTRIM(SubTotal) as [SubTotal],RTRIM(VATPer) as [Vat+ST %],RTRIM(VATAmount) as [VAT+ST Amount],RTRIM(DiscountPer) as [Discount %],RTRIM(DiscountAmount) as [Discount Amount],RTRIM(GrandTotal) as [Grand Total],RTRIM(TotalPayment) as [Total Payment],RTRIM(PaymentDue) as [Payment Due],RTRIM(PaymentType) as [Payment Type],RTRIM(Status) as [Status],Remarks from Invoice_Info,Customer where Invoice_Info.CustomerID=Customer.CustomerID and InvoiceDate between @d1 and @d2 and Customer.CustomerID='" + txtCustomerID.Text + "' order by InvoiceDate desc", con);
101             cmd.Parameters.Add("@d1", SqlDbType.DateTime, 30, "InvoiceDate").Value = dtpInvoiceDateFrom.Value.Date;
102             cmd.Parameters.Add("@d2", SqlDbType.DateTime, 30, "InvoiceDate").Value = dtpInvoiceDateTo.Value.Date;
103             SqlDataAdapter myDA = new SqlDataAdapter(cmd);
104             DataSet myDataSet = new DataSet();
105             myDA.Fill(myDataSet, "Invoice_Info");
106             myDA.Fill(myDataSet, "Customer");
107             DataGridView1.DataSource = myDataSet.Tables["Customer"].DefaultView;
108             DataGridView1.DataSource = myDataSet.Tables["Invoice_Info"].DefaultView;
109             Int64 sum = 0;
110             Int64 sum1 = 0;
111             Int64 sum2 = 0;
112
113             foreach (DataGridViewRow r in this.DataGridView1.Rows)
114             {
115                 Int64 i = Convert.ToInt64(r.Cells[9].Value);
116                 Int64 j = Convert.ToInt64(r.Cells[10].Value);
117                 Int64 k = Convert.ToInt64(r.Cells[11].Value);
118                 sum = sum + i;
119                 sum1 = sum1 + j;
120                 sum2 = sum2 + k;
121
122             }
123             TextBox1.Text = sum.ToString();
124             TextBox2.Text = sum1.ToString();
125             TextBox3.Text = sum2.ToString();
126
127             con.Close();
128             }
129         catch (Exception ex)
130             {
131             MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
132             }
133         }