private void CariButton_Click(object sender, EventArgs e) { string dateFrom, dateTo; bool result; dateFrom = String.Format(culture, "{0:yyyyMMdd}", Convert.ToDateTime(datefromPicker.Value)); dateTo = String.Format(culture, "{0:yyyyMMdd}", Convert.ToDateTime(datetoPicker.Value)); DS.mySqlConnect(); string sqlCommandx = ""; string supplier = ""; string customer = ""; string branch = ""; if (ErrorLabel.Visible != true && checkBox1.Checked == true) { supplier = "AND PH.SUPPLIER_ID = " + SupplierNameCombobox.SelectedValue + " "; customer = "AND SH.CUSTOMER_ID = " + CustomercomboBox.SelectedValue + " "; branch = "AND PH.BRANCH_ID_TO = " + BranchcomboBox.SelectedValue + " "; } switch (originModuleID) { case globalConstants.REPORT_DEBT_PAYMENT: sqlCommandx = "SELECT D.DEBT_ID AS' ID', D.DEBT_DUE_DATE AS 'JT', D.DEBT_NOMINAL AS 'TOTAL', D.PURCHASE_INVOICE AS 'INVOICE', MS.SUPPLIER_FULL_NAME AS 'SUPPLIER', " + "MAX(PD.PAYMENT_CONFIRMED_DATE) AS 'TANGGALLUNAS' " + "FROM DEBT D, PURCHASE_HEADER PH, MASTER_SUPPLIER MS, PAYMENT_DEBT PD " + "WHERE D.DEBT_PAID = 1 AND D.PURCHASE_INVOICE = PH.PURCHASE_INVOICE AND PH.SUPPLIER_ID = MS.SUPPLIER_ID AND D.DEBT_ID = PD.DEBT_ID " + supplier + "AND DATE_FORMAT(PD.PAYMENT_CONFIRMED_DATE, '%Y%m%d') >= '" + dateFrom + "' AND DATE_FORMAT(PD.PAYMENT_CONFIRMED_DATE, '%Y%m%d') <= '" + dateTo + "'"; DS.writeXML(sqlCommandx, globalConstants.DebtPaidXML); ReportDebtPaidForm displayedForm1 = new ReportDebtPaidForm(); displayedForm1.ShowDialog(this); break; case globalConstants.REPORT_CREDIT_PAYMENT: sqlCommandx = "SELECT C.SALES_INVOICE AS 'INVOICE', C.CREDIT_NOMINAL AS 'TOTAL', C.CREDIT_DUE_DATE AS 'JT', " + "MAX(PC.PAYMENT_CONFIRMED_DATE) AS 'LUNAS', MC.CUSTOMER_FULL_NAME " + "FROM CREDIT C, PAYMENT_CREDIT PC, SALES_HEADER SH, MASTER_CUSTOMER MC " + "WHERE C.SALES_INVOICE IS NOT NULL AND C.CREDIT_PAID = 1 AND C.CREDIT_ID = PC.CREDIT_ID AND C.SALES_INVOICE = SH.SALES_INVOICE AND SH.CUSTOMER_ID = MC.CUSTOMER_ID " + customer + "AND DATE_FORMAT(PC.PAYMENT_CONFIRMED_DATE, '%Y%m%d') >= '" + dateFrom + "' AND DATE_FORMAT(PC.PAYMENT_CONFIRMED_DATE, '%Y%m%d') <= '" + dateTo + "'"; DS.writeXML(sqlCommandx, globalConstants.CreditPaidXML); ReportCreditPaidForm displayedForm2 = new ReportCreditPaidForm(); displayedForm2.ShowDialog(this); break; case globalConstants.REPORT_MUTATION_PAYMENT: sqlCommandx = "SELECT C.PM_INVOICE AS 'INVOICE', C.CREDIT_NOMINAL AS 'TOTAL', C.CREDIT_DUE_DATE AS 'JT', " + "MAX(PC.PAYMENT_CONFIRMED_DATE) AS 'LUNAS', MB.BRANCH_NAME " + "FROM CREDIT C, PAYMENT_CREDIT PC, PRODUCTS_MUTATION_HEADER PH, MASTER_BRANCH MB " + "WHERE C.PM_INVOICE IS NOT NULL AND C.CREDIT_PAID = 1 AND C.CREDIT_ID = PC.CREDIT_ID AND C.PM_INVOICE = PH.PM_INVOICE AND PH.BRANCH_ID_TO = MB.BRANCH_ID " + branch + "AND DATE_FORMAT(PC.PAYMENT_CONFIRMED_DATE, '%Y%m%d') >= '" + dateFrom + "' AND DATE_FORMAT(PC.PAYMENT_CONFIRMED_DATE, '%Y%m%d') <= '" + dateTo + "'"; DS.writeXML(sqlCommandx, globalConstants.MutationPaidXML); ReportCreditPaidForm displayedForm3 = new ReportCreditPaidForm(); displayedForm3.ShowDialog(this); break; } }
private void CariButton_Click(object sender, EventArgs e) { string dateFrom, dateTo; int cust_id = 0; string prod_id = ""; bool result; dateFrom = String.Format(culture, "{0:yyyyMMdd}", Convert.ToDateTime(datefromPicker.Value)); dateTo = String.Format(culture, "{0:yyyyMMdd}", Convert.ToDateTime(datetoPicker.Value)); DS.mySqlConnect(); string sqlCommandx = ""; string supplier = ""; string produk = ""; if (ErrorLabel.Visible == true) { supplier = "AND PH.SUPPLIER_ID = '" + SupplierNameCombobox.SelectedValue + "' "; produk = "AND PD.PRODUCT_ID = '" + ProductcomboBox.SelectedValue + "' "; } switch (originModuleID) { case globalConstants.REPORT_PURCHASE_SUMMARY: sqlCommandx = "SELECT PH.PURCHASE_DATETIME AS 'TGL', PH.PURCHASE_DATE_RECEIVED AS 'TERIMA', PURCHASE_INVOICE AS 'INVOICE', MS.SUPPLIER_FULL_NAME AS 'SUPPLIER', PH.PURCHASE_TOTAL AS 'TOTAL', IF(PH.PURCHASE_TERM_OF_PAYMENT>0,'KREDIT','TUNAI') AS 'TOP', PH.PURCHASE_TERM_OF_PAYMENT_DURATION AS 'HARI', IF(PH.PURCHASE_PAID>0,'LUNAS','BELUM LUNAS') AS 'STATUS' " + "FROM PURCHASE_HEADER PH, MASTER_SUPPLIER MS " + "WHERE PH.SUPPLIER_ID = MS.SUPPLIER_ID " + supplier + "AND DATE_FORMAT(PH.PURCHASE_DATETIME, '%Y%m%d') >= '" + dateFrom + "' AND DATE_FORMAT(PH.PURCHASE_DATETIME, '%Y%m%d') <= '" + dateTo + "' " + "ORDER BY TGL"; DS.writeXML(sqlCommandx, globalConstants.PurchaseSummaryXML); ReportPurchaseSummaryForm displayedForm1 = new ReportPurchaseSummaryForm(); displayedForm1.ShowDialog(this); break; case globalConstants.REPORT_PURCHASE_DETAILED: sqlCommandx = "SELECT PH.PURCHASE_DATETIME AS 'TGL', PH.PURCHASE_DATE_RECEIVED AS 'TERIMA', PH.PURCHASE_INVOICE AS 'INVOICE', MS.SUPPLIER_FULL_NAME AS 'SUPPLIER', MP.PRODUCT_NAME AS 'PRODUK', PD.PRODUCT_PRICE AS 'HARGA', PD.PRODUCT_QTY AS 'QTY', PD.PURCHASE_SUBTOTAL AS 'SUBTOTAL' " + "FROM PURCHASE_HEADER PH, PURCHASE_DETAIL PD, MASTER_SUPPLIER MS, MASTER_PRODUCT MP " + "WHERE PD.PURCHASE_INVOICE = PH.PURCHASE_INVOICE AND PH.SUPPLIER_ID = MS.SUPPLIER_ID " + supplier + "AND PD.PRODUCT_ID = MP.PRODUCT_ID AND DATE_FORMAT(PH.PURCHASE_DATETIME, '%Y%m%d') >= '" + dateFrom + "' AND DATE_FORMAT(PH.PURCHASE_DATETIME, '%Y%m%d') <= '" + dateTo + "' " + "ORDER BY TGL,INVOICE,PRODUK"; DS.writeXML(sqlCommandx, globalConstants.PurchaseDetailedXML); ReportPurchaseDetailedForm displayedForm2 = new ReportPurchaseDetailedForm(); displayedForm2.ShowDialog(this); break; case globalConstants.REPORT_PURCHASE_ByPRODUCT: sqlCommandx = "SELECT DATE(PH.PURCHASE_DATETIME) AS 'TGL', PH.PURCHASE_DATE_RECEIVED AS 'TERIMA', PH.PURCHASE_INVOICE AS 'INVOICE', MS.SUPPLIER_FULL_NAME AS 'SUPPLIER', MP.PRODUCT_NAME AS 'PRODUK', PD.PRODUCT_PRICE AS 'HARGA', PD.PRODUCT_QTY AS 'QTY', PD.PURCHASE_SUBTOTAL AS 'SUBTOTAL' " + "FROM PURCHASE_HEADER PH, PURCHASE_DETAIL PD, MASTER_SUPPLIER MS, MASTER_PRODUCT MP " + "WHERE PD.PURCHASE_INVOICE = PH.PURCHASE_INVOICE AND PH.SUPPLIER_ID = MS.SUPPLIER_ID AND PD.PRODUCT_ID = MP.PRODUCT_ID " + produk + "AND DATE_FORMAT(PH.PURCHASE_DATETIME, '%Y%m%d') >= '" + dateFrom + "' AND DATE_FORMAT(PH.PURCHASE_DATETIME, '%Y%m%d') <= '" + dateTo + "' " + "ORDER BY TGL"; DS.writeXML(sqlCommandx, globalConstants.PurchasebyProductXML); ReportPurchasebyProductForm displayedForm3 = new ReportPurchasebyProductForm(); displayedForm3.ShowDialog(this); break; } }
private void printOutReturPembelian(string noRetur) { string returNo = noRetur; string sqlCommandx = ""; sqlCommandx = "SELECT 'RETUR PEMBELIAN' AS MODULE_TYPE, RPH.RP_ID AS 'NO_RETUR', IFNULL(MS.SUPPLIER_FULL_NAME, 'HQ PUSAT') AS 'NAME', RPH.RP_DATE AS 'RETUR_DATE', RPH.RP_TOTAL AS 'RETUR_TOTAL', MP.PRODUCT_NAME AS 'PRODUCT_NAME', RPD.PRODUCT_BASEPRICE AS 'PRICE', RPD.PRODUCT_QTY AS 'QTY', RPD.RP_DESCRIPTION AS 'DESC', RPD.RP_SUBTOTAL AS 'SUBTOTAL' " + "FROM RETURN_PURCHASE_HEADER RPH LEFT OUTER JOIN MASTER_SUPPLIER MS ON RPH.SUPPLIER_ID = MS.SUPPLIER_ID, MASTER_PRODUCT MP, RETURN_PURCHASE_DETAIL RPD " + "WHERE RPD.RP_ID = RPH.RP_ID AND RPD.PRODUCT_ID = MP.PRODUCT_ID AND RPH.RP_ID = '" + returNo + "'"; DS.writeXML(sqlCommandx, globalConstants.returPermintaanXML); dataReturPermintaanPrintOutForm displayForm = new dataReturPermintaanPrintOutForm(); displayForm.ShowDialog(this); }
private void printReport(string invoiceNo, string mutasiNo, string poNo) { string sqlCommandx = ""; if (mutasiNo.Length > 0) { sqlCommandx = "SELECT '1' AS TYPE, '" + mutasiNo + "' AS ORIGIN_INVOICE, DATE(PH.PR_DATE) AS 'TGL', PH.PR_INVOICE AS 'INVOICE', MP.PRODUCT_NAME AS 'PRODUK', PD.PRODUCT_BASE_PRICE AS 'HARGA', PD.PRODUCT_QTY AS 'QTY', PD.PR_SUBTOTAL AS 'SUBTOTAL' " + "FROM PRODUCTS_RECEIVED_HEADER PH, PRODUCTS_RECEIVED_DETAIL PD, MASTER_PRODUCT MP " + "WHERE PH.PR_INVOICE = '" + invoiceNo + "' AND PD.PR_INVOICE = PH.PR_INVOICE AND PD.PRODUCT_ID = MP.PRODUCT_ID"; } else if (poNo.Length > 0) { sqlCommandx = "SELECT '2' AS TYPE, '" + poNo + "' AS ORIGIN_INVOICE, DATE(PH.PR_DATE) AS 'TGL', PH.PR_INVOICE AS 'INVOICE', MP.PRODUCT_NAME AS 'PRODUK', PD.PRODUCT_BASE_PRICE AS 'HARGA', PD.PRODUCT_QTY AS 'QTY', PD.PR_SUBTOTAL AS 'SUBTOTAL' " + "FROM PRODUCTS_RECEIVED_HEADER PH, PRODUCTS_RECEIVED_DETAIL PD, MASTER_PRODUCT MP " + "WHERE PH.PR_INVOICE = '" + invoiceNo + "' AND PD.PR_INVOICE = PH.PR_INVOICE AND PD.PRODUCT_ID = MP.PRODUCT_ID"; } else { sqlCommandx = "SELECT '0' AS TYPE, 'AA' AS ORIGIN_INVOICE, DATE(PH.PR_DATE) AS 'TGL', PH.PR_INVOICE AS 'INVOICE', MP.PRODUCT_NAME AS 'PRODUK', PD.PRODUCT_BASE_PRICE AS 'HARGA', PD.PRODUCT_QTY AS 'QTY', PD.PR_SUBTOTAL AS 'SUBTOTAL' " + "FROM PRODUCTS_RECEIVED_HEADER PH, PRODUCTS_RECEIVED_DETAIL PD, MASTER_PRODUCT MP " + "WHERE PH.PR_INVOICE = '" + invoiceNo + "' AND PD.PR_INVOICE = PH.PR_INVOICE AND PD.PRODUCT_ID = MP.PRODUCT_ID"; } //"WHERE PD.PURCHASE_INVOICE = PH.PURCHASE_INVOICE AND PH.SUPPLIER_ID = MS.SUPPLIER_ID " + supplier + "AND PD.PRODUCT_ID = MP.PRODUCT_ID AND DATE_FORMAT(PH.PURCHASE_DATETIME, '%Y%m%d') >= '" + dateFrom + "' AND DATE_FORMAT(PH.PURCHASE_DATETIME, '%Y%m%d') <= '" + dateTo + "' " + //"ORDER BY TGL,INVOICE,PRODUK"; DS.writeXML(sqlCommandx, globalConstants.penerimaanBarangXML); penerimaanBarangPrintOutForm displayForm = new penerimaanBarangPrintOutForm(); displayForm.ShowDialog(this); }
private void CariButton_Click(object sender, EventArgs e) { string dateFrom, dateTo; bool result; dateFrom = String.Format(culture, "{0:yyyyMMdd}", Convert.ToDateTime(datefromPicker.Value)); dateTo = String.Format(culture, "{0:yyyyMMdd}", Convert.ToDateTime(datetoPicker.Value)); DS.mySqlConnect(); string sqlCommandx = ""; string user_id = ""; if (ErrorLabel.Visible == false) { user_id = "AND CL.USER_ID = " + UserIDCombobox.SelectedValue + " "; } sqlCommandx = "SELECT MU.USER_FULL_NAME AS 'USERID', CL.DATE_LOGIN AS 'LOGIN',CL.DATE_LOGOUT AS 'LOGOUT', CL.AMOUNT_START AS 'START', CL.AMOUNT_END AS 'END', " + "CL.COMMENT AS 'COMMENT', CL.TOTAL_CASH_TRANSACTION AS 'CASH', CL.TOTAL_NON_CASH_TRANSACTION AS 'NONCASH',CL.TOTAL_OTHER_TRANSACTION AS 'OTHER', " + "SH.SALES_INVOICE AS 'INVOICE', SH.SALES_DATE AS 'TGLTRANS', IF(SH.SALES_TOP = 1, 'TUNAI', 'CREDIT') AS 'TOP', SH.SALES_TOTAL AS 'TOTAL' " + "FROM CASHIER_LOG CL, SALES_HEADER SH, MASTER_USER MU " + "WHERE SH.SALES_DATE >= CL.DATE_LOGIN AND SH.SALES_DATE <= CL.DATE_LOGOUT " + "AND DATE_FORMAT(CL.DATE_LOGIN, '%Y%m%d') >= '" + dateFrom + "' AND DATE_FORMAT(CL.DATE_LOGIN, '%Y%m%d') <= '" + dateTo + "' " + "AND CL.USER_ID = MU.ID " + user_id + "GROUP BY INVOICE " + "ORDER BY TGLTRANS ASC"; DS.writeXML(sqlCommandx, globalConstants.CashierLogXML); ReportCashierLogForm displayedForm1 = new ReportCashierLogForm(); displayedForm1.ShowDialog(this); }
private void CetakButton_Click(object sender, EventArgs e) { //preview laporan DS.mySqlConnect(); DS.writeXML(sqlCommandx, globalConstants.UserXML); ReportUserForm displayedform = new ReportUserForm(); displayedform.ShowDialog(this); }
private void button1_Click(object sender, EventArgs e) { DS.mySqlConnect(); if (textBox1.Text.Equals("")) { MessageBox.Show("Error no SQL Command"); } else { String sqlcmd = textBox1.Text; if (textBox2.Text.Equals("")) { DS.writeXML(sqlcmd); //done writing XML report } else { DS.writeXML(sqlcmd, textBox2.Text); } } }
private void printOutPurchaseOrder(string PONo) { string sqlCommandx = "SELECT PH.PURCHASE_DATETIME AS 'TGL', PH.PURCHASE_DATE_RECEIVED AS 'TERIMA', PH.PURCHASE_INVOICE AS 'INVOICE', MS.SUPPLIER_FULL_NAME AS 'SUPPLIER', MP.PRODUCT_NAME AS 'PRODUK', PD.PRODUCT_PRICE AS 'HARGA', PD.PRODUCT_QTY AS 'QTY', PD.PURCHASE_SUBTOTAL AS 'SUBTOTAL' " + "FROM PURCHASE_HEADER PH, PURCHASE_DETAIL PD, MASTER_SUPPLIER MS, MASTER_PRODUCT MP " + "WHERE PH.PURCHASE_INVOICE = '" + PONo + "' AND PH.SUPPLIER_ID = MS.SUPPLIER_ID AND PD.PURCHASE_INVOICE = PH.PURCHASE_INVOICE AND PD.PRODUCT_ID = MP.PRODUCT_ID"; DS.writeXML(sqlCommandx, globalConstants.purchaseOrderXML); purchaseOrderPrintOutForm displayForm = new purchaseOrderPrintOutForm(); displayForm.ShowDialog(this); }
private void button2_Click(object sender, EventArgs e) { //PRINT BARCODE string sqlCommandx = "SELECT PRODUCT_ID AS 'ID', CONCAT('*',PRODUCT_BARCODE,'*') AS 'BARCODE1', PRODUCT_BARCODE AS 'BARCODE2', PRODUCT_NAME AS 'NAME', PRODUCT_BRAND AS ' BRAND', PRODUCT_RETAIL_PRICE AS 'PRICE'" + " FROM master_product" + " WHERE PRODUCT_ID = '" + kodeProdukTextBox.Text + "'"; DS.writeXML(sqlCommandx, globalConstants.PrintBarcodeXML); PrintBarcodeForm displayedForm = new PrintBarcodeForm(); displayedForm.ShowDialog(this); }
private void CariButton_Click(object sender, EventArgs e) { string dateFrom, dateTo; int cust_id = 0; int supplier_id = 0; int tags_id = 0; string prod_id = ""; bool result; dateFrom = String.Format(culture, "{0:yyyyMMdd}", Convert.ToDateTime(datefromPicker.Value)); dateTo = String.Format(culture, "{0:yyyyMMdd}", Convert.ToDateTime(datetoPicker.Value)); DS.mySqlConnect(); string sqlCommandx = ""; string supplier = " "; string customer = " "; string produk = " "; string tags = " "; if (checkBox1.Checked == false) { switch (originModuleID) { case globalConstants.REPORT_PURCHASE_RETURN: result = int.TryParse(SupplierNameCombobox.SelectedValue.ToString(), out supplier_id); supplier = "AND RH.SUPPLIER_ID"; supplier = " = " + supplier_id + " ";; break; case globalConstants.REPORT_SALES_RETURN: result = int.TryParse(CustomercomboBox.SelectedValue.ToString(), out cust_id); if (cust_id > 0) { customer = "AND RH.CUSTOMER_ID"; customer = " = " + cust_id + " "; } break; } } if (checkBox2.Checked == false) { switch (originModuleID) { case globalConstants.REPORT_STOCK: result = int.TryParse(TagsComboBox.SelectedValue.ToString(), out tags_id); if (tags_id > 0) { tags = "AND PC.CATEGORY_ID"; tags = " = " + tags_id + " "; } break; default: produk = "AND PA.PRODUCT_ID"; //switch (originModuleID) //{ // case globalConstants.REPORT_PURCHASE_RETURN: // produk = "AND RD.PRODUCT_ID"; // break; // case globalConstants.REPORT_SALES_RETURN: // produk = "AND RD.PRODUCT_ID"; // break; // case globalConstants.REPORT_REQUEST_RETURN: // produk = "AND RD.PRODUCT_ID"; // break; // case globalConstants.REPORT_PRODUCT_MUTATION: // produk = "AND MD.PRODUCT_ID"; // break; // case globalConstants.REPORT_STOCK_DEVIATION: // produk = "AND PA.PRODUCT_ID"; // break; //} produk = produk + " = '" + ProductcomboBox.SelectedValue + "' "; break; } } switch (originModuleID) { case globalConstants.REPORT_PURCHASE_RETURN: sqlCommandx = "SELECT RH.RP_ID AS 'ID', MS.SUPPLIER_FULL_NAME AS 'SUPPLIER', RH.RP_DATE AS 'TANGGAL', RH.RP_TOTAL AS 'TOTAL', IF(RH.RP_PROCESSED=1,'SUDAH DIPROSES','BELUM DIPROSES') AS 'STATUS', " + "MP.PRODUCT_NAME AS 'PRODUK', RD.PRODUCT_BASEPRICE AS 'HARGA', RD.PRODUCT_QTY AS 'QTY', RD.RP_DESCRIPTION AS 'DESKRIPSI', RD.RP_SUBTOTAL AS 'SUBTOTAL' " + "FROM RETURN_PURCHASE_HEADER RH, MASTER_SUPPLIER MS, RETURN_PURCHASE_DETAIL RD, MASTER_PRODUCT MP " + "WHERE RH.SUPPLIER_ID = MS.SUPPLIER_ID AND RH.RP_ID = RD.RP_ID AND RD.PRODUCT_ID = MP.PRODUCT_ID " + produk + supplier + "AND DATE_FORMAT(RH.RP_DATE, '%Y%m%d') >= '" + dateFrom + "' AND DATE_FORMAT(RH.RP_DATE, '%Y%m%d') <= '" + dateTo + "' " + "GROUP BY RD.ID"; DS.writeXML(sqlCommandx, globalConstants.PurchaseReturnXML); ReportPurchaseReturnForm displayedForm1 = new ReportPurchaseReturnForm(); displayedForm1.ShowDialog(this); break; case globalConstants.REPORT_SALES_RETURN: sqlCommandx = "SELECT RH.RS_INVOICE AS 'INVOICE', RH.SALES_INVOICE AS 'NOTAJUAL', MC.CUSTOMER_FULL_NAME AS 'CUSTOMER', RH.RS_DATETIME AS 'TANGGAL', " + "RH.RS_TOTAL AS 'TOTAL', MP.PRODUCT_NAME AS 'PRODUK', RD.PRODUCT_SALES_PRICE AS 'HARGAJUAL', RD.PRODUCT_SALES_QTY AS 'JMLJUAL', " + "RD.PRODUCT_RETURN_QTY AS 'JMLRETUR', RD.RS_DESCRIPTION AS 'DESKRIPSI', RD.RS_SUBTOTAL AS 'SUBTOTAL' " + "FROM RETURN_SALES_HEADER RH, MASTER_CUSTOMER MC, RETURN_SALES_DETAIL RD, MASTER_PRODUCT MP " + "WHERE RH.CUSTOMER_ID = MC.CUSTOMER_ID AND RH.RS_INVOICE = RD.RS_INVOICE AND RD.PRODUCT_ID = MP.PRODUCT_ID " + produk + customer + "AND DATE_FORMAT(RH.RS_DATETIME, '%Y%m%d') >= '" + dateFrom + "' AND DATE_FORMAT(RH.RS_DATETIME, '%Y%m%d') <= '" + dateTo + "' " + "GROUP BY RD.ID UNION " + "SELECT RH.RS_INVOICE AS 'INVOICE', RH.SALES_INVOICE AS 'NOTAJUAL', 'P-UMUM' AS 'CUSTOMER', RH.RS_DATETIME AS 'TANGGAL', " + "RH.RS_TOTAL AS 'TOTAL', MP.PRODUCT_NAME AS 'PRODUK', RD.PRODUCT_SALES_PRICE AS 'HARGAJUAL', RD.PRODUCT_SALES_QTY AS 'JMLJUAL', " + "RD.PRODUCT_RETURN_QTY AS 'JMLRETUR', RD.RS_DESCRIPTION AS 'DESKRIPSI', RD.RS_SUBTOTAL AS 'SUBTOTAL' " + "FROM RETURN_SALES_HEADER RH, RETURN_SALES_DETAIL RD, MASTER_PRODUCT MP " + "WHERE RH.CUSTOMER_ID = 0 AND RH.RS_INVOICE = RD.RS_INVOICE AND RD.PRODUCT_ID = MP.PRODUCT_ID " + produk + "AND DATE_FORMAT(RH.RS_DATETIME, '%Y%m%d') >= '" + dateFrom + "' AND DATE_FORMAT(RH.RS_DATETIME, '%Y%m%d') <= '" + dateTo + "' " + "GROUP BY RD.ID"; DS.writeXML(sqlCommandx, globalConstants.SalesReturnXML); ReportSalesReturnForm displayedForm2 = new ReportSalesReturnForm(); displayedForm2.ShowDialog(this); break; case globalConstants.REPORT_REQUEST_RETURN: sqlCommandx = "SELECT RH.RP_ID AS 'ID', RH.RP_DATE AS 'TANGGAL', RH.RP_TOTAL AS 'TOTAL', IF(RH.RP_PROCESSED=1,'SUDAH DIPROSES','BELUM DIPROSES') AS 'STATUS', " + "MP.PRODUCT_NAME AS 'PRODUK', RD.PRODUCT_BASEPRICE AS 'HARGA', RD.PRODUCT_QTY AS 'QTY', RD.RP_DESCRIPTION AS 'DESKRIPSI', RD.RP_SUBTOTAL AS 'SUBTOTAL' " + "FROM RETURN_PURCHASE_HEADER RH, RETURN_PURCHASE_DETAIL RD, MASTER_PRODUCT MP " + "WHERE RH.SUPPLIER_ID = 0 AND RH.RP_ID = RD.RP_ID AND RD.PRODUCT_ID = MP.PRODUCT_ID " + produk + "AND DATE_FORMAT(RH.RP_DATE, '%Y%m%d') >= '" + dateFrom + "' AND DATE_FORMAT(RH.RP_DATE, '%Y%m%d') <= '" + dateTo + "' " + "GROUP BY RD.ID"; DS.writeXML(sqlCommandx, globalConstants.RequestReturnXML); ReportRequestReturnForm displayedForm3 = new ReportRequestReturnForm(); displayedForm3.ShowDialog(this); break; case globalConstants.REPORT_PRODUCT_MUTATION: sqlCommandx = "SELECT MH.PM_INVOICE AS 'INVOICE', MH.PM_DATETIME AS 'TANGGAL', 'PUSAT' AS 'DARI', MB.BRANCH_NAME AS 'KE', MH.PM_TOTAL AS 'TOTAL', " + "IF(MH.PM_RECEIVED = 1, 'DITERIMA', 'BELUM DITERIMA') AS 'STATUS', MP.PRODUCT_NAME AS 'PRODUK', MD.PRODUCT_BASE_PRICE AS 'HPP', " + "MD.PRODUCT_QTY AS 'QTY', MD.PM_SUBTOTAL AS 'SUBTOTAL' " + "FROM PRODUCTS_MUTATION_HEADER MH, MASTER_BRANCH MB, PRODUCTS_MUTATION_DETAIL MD, MASTER_PRODUCT MP " + "WHERE MH.BRANCH_ID_TO = MB.BRANCH_ID AND MH.PM_INVOICE = MD.PM_INVOICE AND MD.PRODUCT_ID = MP.PRODUCT_ID " + produk + "AND DATE_FORMAT(MH.PM_DATETIME, '%Y%m%d') >= '" + dateFrom + "' AND DATE_FORMAT(MH.PM_DATETIME, '%Y%m%d') <= '" + dateTo + "' " + "GROUP BY MD.ID"; DS.writeXML(sqlCommandx, globalConstants.ProductMutationXML); ReportProductMutationForm displayedForm4 = new ReportProductMutationForm(); displayedForm4.ShowDialog(this); break; case globalConstants.REPORT_STOCK_DEVIATION: sqlCommandx = "SELECT PA.PRODUCT_ADJUSTMENT_DATE AS 'DATE', MP.PRODUCT_ID AS 'ID', MP.PRODUCT_NAME AS 'NAMA PRODUK', MP.PRODUCT_DESCRIPTION AS 'DESKRIPSI', " + "MP.PRODUCT_BRAND AS 'MERK', MP.PRODUCT_SHELVES AS 'NOMOR RAK', (PA.PRODUCT_NEW_STOCK_QTY - PA.PRODUCT_OLD_STOCK_QTY) AS 'STOK', MU.UNIT_NAME AS 'SATUAN', " + "IF((PA.PRODUCT_NEW_STOCK_QTY - PA.PRODUCT_OLD_STOCK_QTY) > 0, 'POSITIVE', 'NEGATIVE') AS 'DEVIASI' " + "FROM PRODUCT_ADJUSTMENT PA, MASTER_PRODUCT MP, MASTER_UNIT MU " + "WHERE PA.PRODUCT_ID = MP.PRODUCT_ID AND MP.UNIT_ID = MU.UNIT_ID " + produk + "AND DATE_FORMAT(PA.PRODUCT_ADJUSTMENT_DATE, '%Y%m%d') >= '" + dateFrom + "' AND DATE_FORMAT(PA.PRODUCT_ADJUSTMENT_DATE, '%Y%m%d') <= '" + dateTo + "' " + "GROUP BY PA.ID " + "ORDER BY DEVIASI ASC"; DS.writeXML(sqlCommandx, globalConstants.ProductDeviationXML); ReportStockDeviationForm displayedForm5 = new ReportStockDeviationForm(); displayedForm5.ShowDialog(this); break; case globalConstants.REPORT_STOCK: sqlCommandx = "SELECT MP.PRODUCT_NAME, MP.PRODUCT_STOCK_QTY, MU.UNIT_NAME, MC.CATEGORY_NAME " + "FROM MASTER_PRODUCT MP, PRODUCT_CATEGORY PC, MASTER_CATEGORY MC, MASTER_UNIT MU " + "WHERE MP.PRODUCT_IS_SERVICE = 0 AND MP.PRODUCT_ACTIVE = 1 AND MP.UNIT_ID = MU.UNIT_ID " + "AND MP.PRODUCT_ID = PC.PRODUCT_ID AND PC.CATEGORY_ID = MC.CATEGORY_ID" + tags; DS.writeXML(sqlCommandx, globalConstants.StockXML); ReportStockForm displayedForm6 = new ReportStockForm(); displayedForm6.ShowDialog(this); break; } }
private void CariButton_Click(object sender, EventArgs e) { string dateFrom, dateTo; dateFrom = String.Format(culture, "{0:yyyyMMdd}", Convert.ToDateTime(datefromPicker.Value)); dateTo = String.Format(culture, "{0:yyyyMMdd}", Convert.ToDateTime(datetoPicker.Value)); DS.mySqlConnect(); string sqlCommandx = ""; switch (originModuleID) { case globalConstants.REPORT_FINANCE_IN: sqlCommandx = "SELECT DJ.JOURNAL_DATETIME AS 'TGL', MA.ACCOUNT_NAME AS 'AKUN', DJ.JOURNAL_NOMINAL AS 'JML', DJ.JOURNAL_DESCRIPTION AS 'DESKRIPSI' " + "FROM DAILY_JOURNAL DJ, MASTER_ACCOUNT MA " + "WHERE DJ.ACCOUNT_ID = MA.ACCOUNT_ID AND MA.ACCOUNT_TYPE_ID = 1 AND DJ.BRANCH_ID = 0 " + "AND DATE_FORMAT(DJ.JOURNAL_DATETIME, '%Y%m%d') >= '" + dateFrom + "' AND DATE_FORMAT(DJ.JOURNAL_DATETIME, '%Y%m%d') <= '" + dateTo + "'"; DS.writeXML(sqlCommandx, globalConstants.FinanceInXML); ReportCashierLogForm displayedForm1 = new ReportCashierLogForm(); displayedForm1.ShowDialog(this); break; case globalConstants.REPORT_FINANCE_OUT: sqlCommandx = "SELECT DJ.JOURNAL_DATETIME AS 'TGL', MA.ACCOUNT_NAME AS 'AKUN', DJ.JOURNAL_NOMINAL AS 'JML', DJ.JOURNAL_DESCRIPTION AS 'DESKRIPSI' " + "FROM DAILY_JOURNAL DJ, MASTER_ACCOUNT MA " + "WHERE DJ.ACCOUNT_ID = MA.ACCOUNT_ID AND MA.ACCOUNT_TYPE_ID = 2 AND DJ.BRANCH_ID = 0 " + "AND DATE_FORMAT(DJ.JOURNAL_DATETIME, '%Y%m%d') >= '" + dateFrom + "' AND DATE_FORMAT(DJ.JOURNAL_DATETIME, '%Y%m%d') <= '" + dateTo + "'"; DS.writeXML(sqlCommandx, globalConstants.FinanceOutXML); ReportFinanceOutForm displayedForm2 = new ReportFinanceOutForm(); displayedForm2.ShowDialog(this); break; case globalConstants.REPORT_MARGIN: sqlCommandx = "SELECT DATE(SH.SALES_DATE) AS 'TGL', SUM((SD.SALES_SUBTOTAL-(SD.PRODUCT_QTY*SD.PRODUCT_PRICE))) AS 'MARGIN' " + "FROM SALES_HEADER SH, SALES_DETAIL SD " + "WHERE SH.SALES_PAID = 1 AND SH.SALES_INVOICE = SD.SALES_INVOICE " + "AND DATE_FORMAT(SH.SALES_DATE, '%Y%m%d') >= '" + dateFrom + "' AND DATE_FORMAT(SH.SALES_DATE, '%Y%m%d') <= '" + dateTo + "' " + "GROUP BY DATE(SH.SALES_DATE)"; DS.writeXML(sqlCommandx, globalConstants.MarginXML); ReportMarginForm displayedForm3 = new ReportMarginForm(); displayedForm3.ShowDialog(this); break; case globalConstants.REPORT_MONTHLY_BALANCE: int days = DateTime.DaysInMonth(Int32.Parse(MonthPicker.Value.ToString("yyyy")), Int32.Parse(MonthPicker.Value.ToString("MM"))); string monthname = MonthPicker.Value.ToString("MMMM"); DS.beginTransaction(); try { DS.mySqlConnect(); sqlCommandx = "DROP TABLE `daysmonth`"; DS.executeNonQueryCommand(sqlCommandx); sqlCommandx = "CREATE TABLE `daysmonth` (" + "`TGL` tinyint(4) unsigned NOT NULL," + "PRIMARY KEY (`TGL`)" + ") ENGINE = InnoDB DEFAULT CHARSET = utf8"; DS.executeNonQueryCommand(sqlCommandx); for (int i = 1; i <= days; i++) { sqlCommandx = "INSERT INTO `daysmonth` (`TGL`) VALUES (" + i + ")"; DS.executeNonQueryCommand(sqlCommandx); } DS.commit(); } catch (Exception excp) { try { //myTrans.Rollback(); } catch (MySqlException ex) { if (DS.getMyTransConnection() != null) { MessageBox.Show("An exception of type " + ex.GetType() + " was encountered while attempting to roll back the transaction."); } } MessageBox.Show("An exception of type " + e.GetType() + " was encountered while inserting the data."); MessageBox.Show("Neither record was written to database."); } finally { DS.mySqlClose(); } sqlCommandx = "SELECT tab1.TGL,tab1.DEBET,IF(tab2.KREDIT IS NULL,0,tab2.KREDIT) AS 'KREDIT' from " + "(SELECT tab1.TGL, IF(tab1.DEBET IS NULL, 0, tab1.DEBET) + IF(tab2.debet IS NULL, 0, tab2.DEBET) as 'DEBET' FROM " + "(SELECT TAB1.TGL, TAB2.DEBET from(SELECT TGL from daysmonth) tab1 left outer join(SELECT DATE_FORMAT(DJ.JOURNAL_DATETIME, '%d') AS 'TGL', " + "DJ.JOURNAL_NOMINAL AS 'DEBET' FROM DAILY_JOURNAL DJ, MASTER_ACCOUNT MA " + "WHERE DJ.ACCOUNT_ID = MA.ACCOUNT_ID AND MA.ACCOUNT_TYPE_ID = 1 AND DJ.BRANCH_ID = 0 " + "AND DATE_FORMAT(DJ.JOURNAL_DATETIME, '%Y%m%d') >= '" + dateFrom + "' AND DATE_FORMAT(DJ.JOURNAL_DATETIME, '%Y%m%d') <= '" + dateTo + "') tab2 " + "on tab1.TGL = tab2.TGL) tab1 " + "left outer join " + "(SELECT DATE_FORMAT(SH.SALES_DATE, '%d') AS 'TGL', IF(SUM((SD.SALES_SUBTOTAL - (SD.PRODUCT_QTY * SD.PRODUCT_PRICE))) IS NULL, 0, SUM((SD.SALES_SUBTOTAL - (SD.PRODUCT_QTY * SD.PRODUCT_PRICE)))) AS 'DEBET' " + "FROM SALES_HEADER SH, SALES_DETAIL SD " + "WHERE SH.SALES_PAID = 1 AND SH.SALES_INVOICE = SD.SALES_INVOICE " + "AND DATE_FORMAT(SH.SALES_DATE, '%Y%m%d') >= '" + dateFrom + "' AND DATE_FORMAT(SH.SALES_DATE, '%Y%m%d') <= '" + dateTo + "' " + "GROUP BY DATE(SH.SALES_DATE)) tab2 " + "on tab1.TGL = tab2.TGL) tab1 " + "left outer join " + "(SELECT DATE_FORMAT(DJ.JOURNAL_DATETIME, '%d') AS 'TGL', IF(SUM(DJ.JOURNAL_NOMINAL) IS NULL, 0, -SUM(DJ.JOURNAL_NOMINAL)) AS 'KREDIT' " + "FROM DAILY_JOURNAL DJ, MASTER_ACCOUNT MA " + "WHERE DJ.ACCOUNT_ID = MA.ACCOUNT_ID AND MA.ACCOUNT_TYPE_ID = 2 AND DJ.BRANCH_ID = 0 " + "AND DATE_FORMAT(DJ.JOURNAL_DATETIME, '%Y%m%d') >= '" + dateFrom + "' AND DATE_FORMAT(DJ.JOURNAL_DATETIME, '%Y%m%d') <= '" + dateTo + "' " + "GROUP BY DATE(DJ.JOURNAL_DATETIME)) tab2 " + "on tab1.TGL = tab2.TGL"; DS.writeXML(sqlCommandx, globalConstants.MonthlyBalanceXML); ReportMonthlyBalanceForm displayedForm4 = new ReportMonthlyBalanceForm(monthname); displayedForm4.ShowDialog(this); break; } }
private void CariButton_Click(object sender, EventArgs e) { string dateFrom, dateTo; string query_product = ""; string query_customer = ""; string query_union_customer = ""; string query_tax = ""; int cust_id = 0; string prod_id = ""; bool result; dateFrom = String.Format(culture, "{0:yyyyMMdd}", Convert.ToDateTime(datefromPicker.Value)); dateTo = String.Format(culture, "{0:yyyyMMdd}", Convert.ToDateTime(datetoPicker.Value)); DS.mySqlConnect(); string sqlCommandx = ""; switch (originModuleID) { case globalConstants.REPORT_SALES_SUMMARY: //result = int.TryParse(CustNameCombobox.SelectedValue.ToString(), out cust_id); //if (result) //{ //} //else //{ // cust_id = 0; //} if (taxModule == false) { sqlCommandx = "SELECT SALES_INVOICE AS 'INVOICE', C.CUSTOMER_FULL_NAME AS 'CUSTOMER', DATE_FORMAT(S.SALES_DATE, '%d-%M-%Y') AS 'DATE',S.SALES_TOTAL AS 'TOTAL', IF(C.CUSTOMER_GROUP=1,'RETAIL',IF(C.CUSTOMER_GROUP=2,'GROSIR','PARTAI')) AS 'GROUP' " + "FROM SALES_HEADER S,MASTER_CUSTOMER C " + "WHERE S.CUSTOMER_ID = C.CUSTOMER_ID AND DATE_FORMAT(S.SALES_DATE, '%Y%m%d') >= '" + dateFrom + "' AND DATE_FORMAT(S.SALES_DATE, '%Y%m%d') <= '" + dateTo + "'" + //AND S.CUSTOMER_ID = " + cust_id + " " + "UNION " + "SELECT S.SALES_INVOICE AS 'INVOICE', 'P-UMUM' AS 'CUSTOMER', DATE_FORMAT(S.SALES_DATE, '%d-%M-%Y') AS 'DATE', S.SALES_TOTAL AS 'TOTAL', 'RETAIL' AS 'GROUP' " + "FROM SALES_HEADER S " + "WHERE DATE_FORMAT(S.SALES_DATE, '%Y%m%d') >= '" + dateFrom + "' AND DATE_FORMAT(S.SALES_DATE, '%Y%m%d') <= '" + dateTo + "' AND S.CUSTOMER_ID = 0"; } else { sqlCommandx = "SELECT SALES_INVOICE AS 'INVOICE', C.CUSTOMER_FULL_NAME AS 'CUSTOMER', DATE_FORMAT(S.SALES_DATE, '%d-%M-%Y') AS 'DATE',S.SALES_TOTAL AS 'TOTAL', IF(C.CUSTOMER_GROUP=1,'RETAIL',IF(C.CUSTOMER_GROUP=2,'GROSIR','PARTAI')) AS 'GROUP' " + "FROM SALES_HEADER_TAX S,MASTER_CUSTOMER C " + "WHERE S.CUSTOMER_ID = C.CUSTOMER_ID AND DATE_FORMAT(S.SALES_DATE, '%Y%m%d') >= '" + dateFrom + "' AND DATE_FORMAT(S.SALES_DATE, '%Y%m%d') <= '" + dateTo + "'" + //AND S.CUSTOMER_ID = " + cust_id + " " + "UNION " + "SELECT S.SALES_INVOICE AS 'INVOICE', 'P-UMUM' AS 'CUSTOMER', DATE_FORMAT(S.SALES_DATE, '%d-%M-%Y') AS 'DATE', S.SALES_TOTAL AS 'TOTAL', 'RETAIL' AS 'GROUP' " + "FROM SALES_HEADER_TAX S " + "WHERE DATE_FORMAT(S.SALES_DATE, '%Y%m%d') >= '" + dateFrom + "' AND DATE_FORMAT(S.SALES_DATE, '%Y%m%d') <= '" + dateTo + "' AND S.CUSTOMER_ID = 0"; } DS.writeXML(sqlCommandx, globalConstants.SalesSummaryXML); ReportSalesSummaryForm displayedForm1 = new ReportSalesSummaryForm(); displayedForm1.ShowDialog(this); break; case globalConstants.REPORT_SALES_DETAILED: // result = int.TryParse(CustNameCombobox.Items[CustNameCombobox.SelectedIndex].ToString(), out cust_id); if (taxModule == false) { query_tax = "SALES_HEADER SH, SALES_DETAIL SD, "; } else { query_tax = "SALES_HEADER_TAX SH, SALES_DETAIL_TAX SD, "; } if (checkBox1.Checked == false) { result = int.TryParse(CustNameCombobox.SelectedValue.ToString(), out cust_id); query_customer = " AND SH.CUSTOMER_ID = " + cust_id; } else { //all query_union_customer = " UNION " + "SELECT SD.ID, SH.SALES_DATE AS 'DATE', SD.SALES_INVOICE AS 'INVOICE', 'P-UMUM' AS 'CUSTOMER', M.PRODUCT_NAME AS 'PRODUCT', SD.PRODUCT_QTY AS 'QTY', " + "SD.PRODUCT_SALES_PRICE AS 'PRICE', ROUND((SD.PRODUCT_QTY * SD.PRODUCT_SALES_PRICE) - SD.SALES_SUBTOTAL, 2) AS 'POTONGAN', SD.SALES_SUBTOTAL AS 'SUBTOTAL', SH.SALES_DISCOUNT_FINAL AS 'DISC', SH.SALES_PAYMENT AS 'PAYMENT', SH.SALES_PAYMENT_CHANGE AS 'CHANGE' " + "FROM " + query_tax + "MASTER_PRODUCT M " + "WHERE SD.PRODUCT_ID = M.PRODUCT_ID AND SD.SALES_INVOICE = SH.SALES_INVOICE AND DATE_FORMAT(SH.SALES_DATE, '%Y%m%d') >= '" + dateFrom + "' AND DATE_FORMAT(SH.SALES_DATE, '%Y%m%d') <= '" + dateTo + "' AND SH.CUSTOMER_ID = 0"; } if (cust_id > 0) { sqlCommandx = "SELECT SD.ID, SH.SALES_DATE AS 'DATE', SD.SALES_INVOICE AS 'INVOICE', MC.CUSTOMER_FULL_NAME AS 'CUSTOMER', M.PRODUCT_NAME AS 'PRODUCT', SD.PRODUCT_QTY AS 'QTY', " + "SD.PRODUCT_SALES_PRICE AS 'PRICE', ROUND((SD.PRODUCT_QTY * SD.PRODUCT_SALES_PRICE) - SD.SALES_SUBTOTAL, 2) AS 'POTONGAN', SD.SALES_SUBTOTAL AS 'SUBTOTAL', SH.SALES_DISCOUNT_FINAL AS 'DISC', SH.SALES_PAYMENT AS 'PAYMENT', SH.SALES_PAYMENT_CHANGE AS 'CHANGE' " + "FROM " + query_tax + "MASTER_PRODUCT M, MASTER_CUSTOMER MC " + "WHERE SH.CUSTOMER_ID = MC.CUSTOMER_ID AND SD.PRODUCT_ID = M.PRODUCT_ID AND SD.SALES_INVOICE = SH.SALES_INVOICE AND DATE_FORMAT(SH.SALES_DATE, '%Y%m%d') >= '" + dateFrom + "' AND DATE_FORMAT(SH.SALES_DATE, '%Y%m%d') <= '" + dateTo + "'" + query_customer + query_union_customer; } else { sqlCommandx = "SELECT SD.ID, SH.SALES_DATE AS 'DATE', SD.SALES_INVOICE AS 'INVOICE', 'P-UMUM' AS 'CUSTOMER', M.PRODUCT_NAME AS 'PRODUCT', SD.PRODUCT_QTY AS 'QTY', " + "SD.PRODUCT_SALES_PRICE AS 'PRICE', ROUND((SD.PRODUCT_QTY * SD.PRODUCT_SALES_PRICE) - SD.SALES_SUBTOTAL, 2) AS 'POTONGAN', SD.SALES_SUBTOTAL AS 'SUBTOTAL', SH.SALES_DISCOUNT_FINAL AS 'DISC', SH.SALES_PAYMENT AS 'PAYMENT', SH.SALES_PAYMENT_CHANGE AS 'CHANGE' " + "FROM " + query_tax + "MASTER_PRODUCT M " + "WHERE SD.PRODUCT_ID = M.PRODUCT_ID AND SD.SALES_INVOICE = SH.SALES_INVOICE AND DATE_FORMAT(SH.SALES_DATE, '%Y%m%d') >= '" + dateFrom + "' AND DATE_FORMAT(SH.SALES_DATE, '%Y%m%d') <= '" + dateTo + "'" + query_customer + query_union_customer; } //if (cust_id > 0) //{ // if (taxModule == false) // { // sqlCommandx = "SELECT SD.ID, SH.SALES_DATE AS 'DATE', SD.SALES_INVOICE AS 'INVOICE', MC.CUSTOMER_FULL_NAME AS 'CUSTOMER', M.PRODUCT_NAME AS 'PRODUCT', SD.PRODUCT_QTY AS 'QTY', " + // "SD.PRODUCT_SALES_PRICE AS 'PRICE', ROUND((SD.PRODUCT_QTY * SD.PRODUCT_SALES_PRICE) - SD.SALES_SUBTOTAL, 2) AS 'POTONGAN', SD.SALES_SUBTOTAL AS 'SUBTOTAL', SH.SALES_DISCOUNT_FINAL AS 'DISC', SH.SALES_PAYMENT AS 'PAYMENT', SH.SALES_PAYMENT_CHANGE AS 'CHANGE' " + // "FROM SALES_HEADER SH, SALES_DETAIL SD, MASTER_PRODUCT M, MASTER_CUSTOMER MC " + // "WHERE SH.CUSTOMER_ID = MC.CUSTOMER_ID AND SD.PRODUCT_ID = M.PRODUCT_ID AND SD.SALES_INVOICE = SH.SALES_INVOICE AND DATE_FORMAT(SH.SALES_DATE, '%Y%m%d') >= '" + dateFrom + "' AND DATE_FORMAT(SH.SALES_DATE, '%Y%m%d') <= '" + dateTo + query_customer; // } // else // { // sqlCommandx = "SELECT SD.ID, SH.SALES_DATE AS 'DATE', SD.SALES_INVOICE AS 'INVOICE', MC.CUSTOMER_FULL_NAME AS 'CUSTOMER', M.PRODUCT_NAME AS 'PRODUCT', SD.PRODUCT_QTY AS 'QTY', " + // "SD.PRODUCT_SALES_PRICE AS 'PRICE', ROUND((SD.PRODUCT_QTY * SD.PRODUCT_SALES_PRICE) - SD.SALES_SUBTOTAL, 2) AS 'POTONGAN', SD.SALES_SUBTOTAL AS 'SUBTOTAL', SH.SALES_DISCOUNT_FINAL AS 'DISC', SH.SALES_PAYMENT AS 'PAYMENT', SH.SALES_PAYMENT_CHANGE AS 'CHANGE' " + // "FROM SALES_HEADER_TAX SH, SALES_DETAIL_TAX SD, MASTER_PRODUCT M, MASTER_CUSTOMER MC " + // "WHERE SH.CUSTOMER_ID = MC.CUSTOMER_ID AND SD.PRODUCT_ID = M.PRODUCT_ID AND SD.SALES_INVOICE = SH.SALES_INVOICE AND DATE_FORMAT(SH.SALES_DATE, '%Y%m%d') >= '" + dateFrom + "' AND DATE_FORMAT(SH.SALES_DATE, '%Y%m%d') <= '" + dateTo + query_customer; // } //} //else //{ // cust_id = 0; // if (taxModule == false) // { // sqlCommandx = "SELECT SD.ID, SH.SALES_DATE AS 'DATE', SD.SALES_INVOICE AS 'INVOICE', 'P-UMUM' AS 'CUSTOMER', M.PRODUCT_NAME AS 'PRODUCT', SD.PRODUCT_QTY AS 'QTY', " + // "SD.PRODUCT_SALES_PRICE AS 'PRICE', ROUND((SD.PRODUCT_QTY * SD.PRODUCT_SALES_PRICE) - SD.SALES_SUBTOTAL, 2) AS 'POTONGAN', SD.SALES_SUBTOTAL AS 'SUBTOTAL', SH.SALES_DISCOUNT_FINAL AS 'DISC', SH.SALES_PAYMENT AS 'PAYMENT', SH.SALES_PAYMENT_CHANGE AS 'CHANGE' " + // "FROM SALES_HEADER SH, SALES_DETAIL SD, MASTER_PRODUCT M " + // "WHERE SD.PRODUCT_ID = M.PRODUCT_ID AND SD.SALES_INVOICE = SH.SALES_INVOICE AND DATE_FORMAT(SH.SALES_DATE, '%Y%m%d') >= '" + dateFrom + "' AND DATE_FORMAT(SH.SALES_DATE, '%Y%m%d') <= '" + dateTo + query_customer; // } // else // { // sqlCommandx = "SELECT SD.ID, SH.SALES_DATE AS 'DATE', SD.SALES_INVOICE AS 'INVOICE', 'P-UMUM' AS 'CUSTOMER', M.PRODUCT_NAME AS 'PRODUCT', SD.PRODUCT_QTY AS 'QTY', " + // "SD.PRODUCT_SALES_PRICE AS 'PRICE', ROUND((SD.PRODUCT_QTY * SD.PRODUCT_SALES_PRICE) - SD.SALES_SUBTOTAL, 2) AS 'POTONGAN', SD.SALES_SUBTOTAL AS 'SUBTOTAL', SH.SALES_DISCOUNT_FINAL AS 'DISC', SH.SALES_PAYMENT AS 'PAYMENT', SH.SALES_PAYMENT_CHANGE AS 'CHANGE' " + // "FROM SALES_HEADER_TAX SH, SALES_DETAIL_TAX SD, MASTER_PRODUCT M " + // "WHERE SD.PRODUCT_ID = M.PRODUCT_ID AND SD.SALES_INVOICE = SH.SALES_INVOICE AND DATE_FORMAT(SH.SALES_DATE, '%Y%m%d') >= '" + dateFrom + "' AND DATE_FORMAT(SH.SALES_DATE, '%Y%m%d') <= '" + dateTo + query_customer; // } //} //sqlCommandx = "SELECT SD.ID, SH.SALES_DATE AS 'DATE', SD.SALES_INVOICE AS 'INVOICE', IFNULL(MC.CUSTOMER_FULL_NAME, 'P-UMUM') AS 'CUSTOMER', M.PRODUCT_NAME AS 'PRODUCT', PRODUCT_QTY AS 'QTY', " + // "PRODUCT_SALES_PRICE AS 'PRICE', ROUND((PRODUCT_QTY * PRODUCT_SALES_PRICE) - SALES_SUBTOTAL, 2) AS 'POTONGAN', SALES_SUBTOTAL AS 'SUBTOTAL', SH.SALES_PAYMENT AS 'PAYMENT', SH.SALES_PAYMENT_CHANGE AS 'CHANGE' " + // "FROM SALES_HEADER SH, SALES_DETAIL SD, MASTER_PRODUCT M, MASTER_CUSTOMER MC " + // "WHERE SH.CUSTOMER_ID = MC.CUSTOMER_ID AND SD.PRODUCT_ID = M.PRODUCT_ID AND SD.SALES_INVOICE = SH.SALES_INVOICE AND DATE_FORMAT(SH.SALES_DATE, '%Y%m%d') >= '" + dateFrom + "' AND DATE_FORMAT(SH.SALES_DATE, '%Y%m%d') <= '" + dateTo + "' AND SH.CUSTOMER_ID = " + cust_id; //" UNION " + //"SELECT SD.ID, SH.SALES_DATE AS 'DATE', SD.SALES_INVOICE AS 'INVOICE', 'P-UMUM' AS 'CUSTOMER', M.PRODUCT_NAME AS 'PRODUCT', PRODUCT_QTY AS 'QTY', PRODUCT_SALES_PRICE AS 'PRICE', " + //"ROUND((PRODUCT_QTY * PRODUCT_SALES_PRICE) - SALES_SUBTOTAL, 2) AS 'POTONGAN', SALES_SUBTOTAL AS 'SUBTOTAL', SH.SALES_PAYMENT AS 'PAYMENT', SH.SALES_PAYMENT_CHANGE AS 'CHANGE' " + //"FROM SALES_HEADER SH, SALES_DETAIL SD, MASTER_PRODUCT M " + //"WHERE SH.CUSTOMER_ID = MC.CUSTOMER_ID AND SD.PRODUCT_ID = M.PRODUCT_ID AND SD.SALES_INVOICE = SH.SALES_INVOICE AND SH.CUSTOMER_ID = 0 AND DATE_FORMAT(SH.SALES_DATE, '%Y%m%d') >= '" + dateFrom + "' AND DATE_FORMAT(SH.SALES_DATE, '%Y%m%d') <= '" + dateTo + "'"; DS.writeXML(sqlCommandx, globalConstants.SalesDetailedXML); ReportSalesDetailedForm displayedForm2 = new ReportSalesDetailedForm(); displayedForm2.ShowDialog(this); break; case globalConstants.REPORT_SALES_PRODUCT: prod_id = ProductcomboBox.SelectedValue.ToString(); if (checkBox1.Checked == false) { query_product = "' AND SD.PRODUCT_ID = '" + prod_id + "'"; } if (taxModule == false) { sqlCommandx = "SELECT SD.ID, SH.SALES_DATE AS 'DATE', SD.SALES_INVOICE AS 'INVOICE', MC.CUSTOMER_FULL_NAME AS 'CUSTOMER', M.PRODUCT_NAME AS 'PRODUCT', PRODUCT_QTY AS 'QTY', PRODUCT_SALES_PRICE AS 'PRICE' " + "FROM SALES_HEADER SH, SALES_DETAIL SD, MASTER_PRODUCT M, MASTER_CUSTOMER MC " + "WHERE SD.PRODUCT_ID = M.PRODUCT_ID AND SD.SALES_INVOICE = SH.SALES_INVOICE AND SH.CUSTOMER_ID = MC.CUSTOMER_ID AND DATE_FORMAT(SH.SALES_DATE, '%Y%m%d') >= '" + dateFrom + "' AND DATE_FORMAT(SH.SALES_DATE, '%Y%m%d') <= '" + dateTo + query_product + " " + "UNION " + "SELECT SD.ID, SH.SALES_DATE AS 'DATE', SD.SALES_INVOICE AS 'INVOICE', 'P-UMUM' AS 'CUSTOMER', M.PRODUCT_NAME AS 'PRODUCT', PRODUCT_QTY AS 'QTY', PRODUCT_SALES_PRICE AS 'PRICE' " + "FROM SALES_HEADER SH, SALES_DETAIL SD, MASTER_PRODUCT M " + "WHERE SD.PRODUCT_ID = M.PRODUCT_ID AND SD.SALES_INVOICE = SH.SALES_INVOICE AND SH.CUSTOMER_ID = 0 AND DATE_FORMAT(SH.SALES_DATE, '%Y%m%d') >= '" + dateFrom + "' AND DATE_FORMAT(SH.SALES_DATE, '%Y%m%d') <= '" + dateTo + query_product; } else { sqlCommandx = "SELECT SD.ID, SH.SALES_DATE AS 'DATE', SD.SALES_INVOICE AS 'INVOICE', MC.CUSTOMER_FULL_NAME AS 'CUSTOMER', M.PRODUCT_NAME AS 'PRODUCT', PRODUCT_QTY AS 'QTY', PRODUCT_SALES_PRICE AS 'PRICE' " + "FROM SALES_HEADER_TAX SH, SALES_DETAIL_TAX SD, MASTER_PRODUCT M, MASTER_CUSTOMER MC " + "WHERE SD.PRODUCT_ID = M.PRODUCT_ID AND SD.SALES_INVOICE = SH.SALES_INVOICE AND SH.CUSTOMER_ID = MC.CUSTOMER_ID AND DATE_FORMAT(SH.SALES_DATE, '%Y%m%d') >= '" + dateFrom + "' AND DATE_FORMAT(SH.SALES_DATE, '%Y%m%d') <= '" + dateTo + query_product + " " + "UNION " + "SELECT SD.ID, SH.SALES_DATE AS 'DATE', SD.SALES_INVOICE AS 'INVOICE', 'P-UMUM' AS 'CUSTOMER', M.PRODUCT_NAME AS 'PRODUCT', PRODUCT_QTY AS 'QTY', PRODUCT_SALES_PRICE AS 'PRICE' " + "FROM SALES_HEADER_TAX SH, SALES_DETAIL_TAX SD, MASTER_PRODUCT M " + "WHERE SD.PRODUCT_ID = M.PRODUCT_ID AND SD.SALES_INVOICE = SH.SALES_INVOICE AND SH.CUSTOMER_ID = 0 AND DATE_FORMAT(SH.SALES_DATE, '%Y%m%d') >= '" + dateFrom + "' AND DATE_FORMAT(SH.SALES_DATE, '%Y%m%d') <= '" + dateTo + query_product; } DS.writeXML(sqlCommandx, globalConstants.SalesbyProductXML); ReportSalesProductForm displayedForm3 = new ReportSalesProductForm(); displayedForm3.ShowDialog(this); break; case globalConstants.REPORT_SALES_OMZET: sqlCommandx = "SELECT SH.SALES_INVOICE AS 'INVOICE', SH.SALES_DATE AS 'DATE', EXTRACT(YEAR_MONTH FROM SH.SALES_DATE) AS 'BULAN', SUM(SH.SALES_TOTAL) AS 'TOTAL', IF(SH.SALES_PAID>0,'LUNAS','BELUM LUNAS') AS 'PAID' " + "FROM SALES_HEADER AS SH " + "WHERE DATE_FORMAT(SH.SALES_DATE, '%Y%m%d') >= '" + dateFrom + "' AND DATE_FORMAT(SH.SALES_DATE, '%Y%m%d') <= '" + dateTo + "' " + "GROUP BY INVOICE " + "ORDER BY PAID,BULAN,DATE ASC"; DS.writeXML(sqlCommandx, globalConstants.SalesOmzetXML); ReportSalesOmzetForm displayedForm4 = new ReportSalesOmzetForm(); displayedForm4.ShowDialog(this); break; } }
private void CariButton_Click(object sender, EventArgs e) { string dateFrom, dateTo; int cust_id = 0; string prod_id = ""; bool result; dateFrom = String.Format(culture, "{0:yyyyMMdd}", Convert.ToDateTime(datefromPicker.Value)); dateTo = String.Format(culture, "{0:yyyyMMdd}", Convert.ToDateTime(datetoPicker.Value)); DS.mySqlConnect(); string sqlCommandx = ""; switch (originModuleID) { case globalConstants.REPORT_TOPSALES_GLOBAL: sqlCommandx = "SELECT MP.PRODUCT_NAME AS 'PRODUCT', SUM(SD.PRODUCT_QTY) AS 'QTY' " + "FROM SALES_DETAIL SD, MASTER_PRODUCT MP " + "WHERE SD.PRODUCT_ID = MP.PRODUCT_ID " + "GROUP BY SD.PRODUCT_ID " + "ORDER BY QTY DESC " + "LIMIT " + LimitTextBox.Text; DS.writeXML(sqlCommandx, globalConstants.TopSalesGlobalXML); ReportTopSalesGlobalForm displayedForm1 = new ReportTopSalesGlobalForm(globalConstants.REPORT_TOPSALES_GLOBAL); displayedForm1.ShowDialog(this); break; case globalConstants.REPORT_TOPSALES_byDATE: sqlCommandx = "SELECT MP.PRODUCT_NAME AS 'PRODUCT', SUM(SD.PRODUCT_QTY) AS 'QTY' " + "FROM SALES_HEADER SH, SALES_DETAIL SD, MASTER_PRODUCT MP " + "WHERE SD.PRODUCT_ID = MP.PRODUCT_ID AND SD.SALES_INVOICE = SH.SALES_INVOICE AND DATE_FORMAT(SH.SALES_DATE, '%Y%m%d') >= '" + dateFrom + "' AND DATE_FORMAT(SH.SALES_DATE, '%Y%m%d') <= '" + dateTo + "' " + "GROUP BY SD.PRODUCT_ID " + "ORDER BY QTY DESC " + "LIMIT " + LimitTextBox.Text; DS.writeXML(sqlCommandx, globalConstants.TopSalesbyDateXML); ReportTopSalesGlobalForm displayedForm2 = new ReportTopSalesGlobalForm(globalConstants.REPORT_TOPSALES_byDATE); displayedForm2.setDateReport(dateTo, dateFrom); displayedForm2.ShowDialog(this); break; case globalConstants.REPORT_TOPSALES_byTAGS: sqlCommandx = "SELECT MC.CATEGORY_ID AS 'ID', MC.CATEGORY_NAME AS 'NAME', MP.PRODUCT_NAME AS 'PRODUCT', SUM(SD.PRODUCT_QTY) AS 'QTY' " + "FROM SALES_DETAIL SD, MASTER_PRODUCT MP, PRODUCT_CATEGORY PC, MASTER_CATEGORY MC " + "WHERE MC.CATEGORY_ID = PC.CATEGORY_ID AND MP.PRODUCT_ID = PC.PRODUCT_ID AND PC.PRODUCT_ID = SD.PRODUCT_ID AND MC.CATEGORY_ID = " + TagscomboBox.SelectedValue.ToString() + " " + "GROUP BY PRODUCT,ID " + "ORDER BY PRODUCT " + "LIMIT " + LimitTextBox.Text; DS.writeXML(sqlCommandx, globalConstants.TopSalesbyTagsXML); ReportTopSalesbyTagsForm displayedForm3 = new ReportTopSalesbyTagsForm(); //displayedForm3.setTags(TagscomboBox.GetItemText(TagscomboBox.SelectedItem)); displayedForm3.ShowDialog(this); break; case globalConstants.REPORT_TOPSALES_ByMARGIN: sqlCommandx = "SELECT MP.PRODUCT_NAME AS 'PRODUCT', SUM(SD.PRODUCT_QTY) AS 'QTY', SUM(SD.SALES_SUBTOTAL-(SD.PRODUCT_QTY*MP.PRODUCT_BASE_PRICE)) AS 'LABA' " + "FROM SALES_HEADER SH, SALES_DETAIL SD, MASTER_PRODUCT MP " + "WHERE SD.PRODUCT_ID = MP.PRODUCT_ID AND SD.SALES_INVOICE = SH.SALES_INVOICE AND DATE_FORMAT(SH.SALES_DATE, '%Y%m%d') >= '" + dateFrom + "' AND DATE_FORMAT(SH.SALES_DATE, '%Y%m%d') <= '" + dateTo + "' " + "GROUP BY SD.PRODUCT_ID " + "ORDER BY LABA DESC " + "LIMIT " + LimitTextBox.Text; DS.writeXML(sqlCommandx, globalConstants.TopSalesbyMarginXML); ReportTopSalesbyMarginForm displayedForm4 = new ReportTopSalesbyMarginForm(); displayedForm4.ShowDialog(this); break; } }