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