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;
}
}
