private void GetData()
{
string whcode = txtwhcode.Text;
string sql = @"SELECT B.WHCODE,B.ABBNAME AS WHNAME,CONVERT(VARCHAR,WORKDATE,103) AS DATE, ABBNO,TOTALB4DISC,DISCOUNTAMT,TOTALB4DISC - DISCOUNTAMT AS NET ,CAST(DVAL AS decimal(18, 2)) AS DVAL
FROM POS_PT_PR A
LEFT JOIN MAS_WH B
ON A.WH_ID = B.ID
WHERE A.PRCODE = (SELECT PRCODE FROM [192.168.10.243].[BeautySystem].[dbo].COMMISSION_PRCODE WHERE CFLAG = 0 AND BRAND = '" + _BID + "')";
sql += "AND A.DVAL IN (SELECT DVAL FROM [192.168.10.243].[BeautySystem].[dbo].COMMISSION_RATE WHERE CFLAG = 0 AND BRAND = '" + _BID + "')";
sql += "AND WORKDATE BETWEEN '" + dtp_StartDate.getDateOnlyForSql() + "' AND '" + dtp_EndDate.getDateOnlyForSql() + "'";
if (txtwhcode.Text != "")
{
sql += "AND WHCODE = '" + whcode + "'";
}
sql += @" UNION ALL
SELECT E.WHCODE,E.WHNAME, CONVERT(VARCHAR, B.INVOICEDATE, 103) AS DATES,A.SALESID AS ABBNO
,CAST((B.SALESBALANCE + B.SUMTAX) AS decimal(18, 2)) AS TOTALB4DISC --ก่อนลด
,CAST(B.ENDDISC AS decimal(18, 2)) AS DISCOUNTAMT --ลด
,CAST(B.INVOICEAMOUNT AS decimal(18, 2)) AS NET --รวมเงินทั้งสิ้น
,CAST(DISCPERCENT AS decimal(18, 2)) AS DVAL
FROM [192.168.10.199].[AODPRD].[dbo].SALESTABLE A with(nolock)
LEFT JOIN [192.168.10.199].[AODPRD].[dbo].CUSTINVOICEJOUR B with(nolock) ON A.SALESID = B.SALESID
LEFT JOIN [192.168.10.199].[AODPRD].[dbo].DEFAULTDIMENSIONVIEW D with(nolock) ON A.DEFAULTDIMENSION = D.DEFAULTDIMENSION
LEFT JOIN [192.168.10.199].[AODPRD].[dbo].BCM_DOC_ST_WH E with(nolock) ON A.SALESID = E.SALESID
WHERE A.CUSTOMERREF LIKE '%WS%' AND (B.INVOICEDATE BETWEEN '" + dtp_StartDate.getDateOnlyForSql() + "' AND '" + dtp_EndDate.getDateOnlyForSql() + "' AND WHCODE LIKE '" + _wh + "%')";
sql += " GROUP BY E.WHCODE,E.WHNAME,A.CUSTOMERREF,B.INVOICEDATE,B.SALESBALANCE,B.SUMTAX ,B.ENDDISC,B.INVOICEAMOUNT ,A.DISCPERCENT,a.SALESPOOLID,A.SALESID";
string Connection = "";
if (_BID == 1)
{
Connection = _cBeauty.GetConnectionBB();
}
else if (_BID == 2)
{
Connection = _cBeauty.GetConnectionBC();
}
DataSet ds = cData.getDataSetWithSqlCommand(Connection, sql, 1000, true);
if (ds.Tables[0].Rows.Count <= 0)
{
cMessage.Error_NoData();
return;
}
lsvData.AddDataWithDataset(ds, true, true, false);
lsvData.SetAlternateColorRow();
}
private void GetData()
{
string Connection = "";
if (_BID == 1)
{
Connection = _cBeauty.GetConnectionBB();
_COMMISSION = 1;
}
else if (_BID == 2)
{
Connection = _cBeauty.GetConnectionBC();
_COMMISSION = 2;
}
string WHCODE = txtwhcode.Text;
string sql = @"SELECT * FROM
(
SELECT
DATES,WHCODE,NET
FROM
(SELECT CONCAT('day_',Datepart(Day,WORKDATE)) AS DATES ,WHCODE,ABBNAME,SUM(TOTAL_NET) AS NET
FROM
(
SELECT AA.WHCODE,AA.ABBNAME,AA.WORKDATE ,AA.ABBNO,AA.STCODE,AA.FULLNAME,AA.TOTAL_NET
FROM (
SELECT WHCODE,B.ABBNAME,WORKDATE,ABBNO,C.STCODE,C.FULLNAME,CAST(net AS decimal(18,2)) AS TOTAL_NET
FROM POS_PT A
LEFT JOIN MAS_WH B
ON A.WH_ID = B.ID
LEFT JOIN MAS_ST C
ON A.ST_ID = C.ID
WHERE PTSTATUS IN ('S','R')
AND WORKDATE BETWEEN '" + dtp_StartDate.getDateOnlyForSql() + @"' AND '" + dtp_EndDate.getDateOnlyForSql() + @"'";
sql += " AND ISNULL(PAY_CARD_CD_ID1,0) <> '" + _FP + "'";
sql += @") AA
LEFT JOIN
(
SELECT B.WHCODE,B.ABBNAME AS WHNAME,WORKDATE, ABBNO,
TOTALB4DISC,DISCOUNTAMT,TOTALB4DISC - DISCOUNTAMT AS NET ,CAST(DVAL AS decimal(18, 2)) AS DVAL
FROM POS_PT_PR A
LEFT JOIN MAS_WH B ON A.WH_ID = B.ID
WHERE A.PRCODE = (
SELECT PRCODE FROM [192.168.10.243].[BeautySystem].[dbo].COMMISSION_PRCODE WHERE CFLAG = 0 AND BRAND = '" + _COMMISSION + @"'
)
AND A.DVAL IN (
SELECT DVAL FROM [192.168.10.243].[BeautySystem].[dbo].COMMISSION_RATE WHERE CFLAG = 0 AND BRAND = '" + _COMMISSION + @"'
)
AND WORKDATE BETWEEN '" + dtp_StartDate.getDateOnlyForSql() + @"' AND '" + dtp_EndDate.getDateOnlyForSql() + @"'
) BB
ON AA.WHCODE = BB.WHCODE AND AA.WORKDATE = BB.WORKDATE AND AA.ABBNO = BB.ABBNO
WHERE BB.WHCODE IS NULL AND BB.WORKDATE IS NULL AND BB.ABBNO IS NULL " ;
if (txtwhcode.Text != "")
{
sql += "AND AA.WHCODE = '" + WHCODE + "'";
}
sql += @") O
GROUP BY WHCODE,ABBNAME,WORKDATE
)aa
) t
PIVOT(
sum(NET)
FOR DATES IN (
[day_1],
[day_2],
[day_3],
[day_4],
[day_5],
[day_6],
[day_7],
[day_8],
[day_9],
[day_10],
[day_11],
[day_12],
[day_13],
[day_14],
[day_15],
[day_16],
[day_17],
[day_18],
[day_19],
[day_20],
[day_21],
[day_22],
[day_23],
[day_24],
[day_25],
[day_26],
[day_27],
[day_28],
[day_29],
[day_30],
[day_31]
)
) AS pivot_table
ORDER BY WHCODE" ;
DataSet ds = cData.getDataSetWithSqlCommand(Connection, sql, 1000, true);
if (ds.Tables[0].Rows.Count <= 0)
{
cMessage.Error_NoData();
return;
}
lsvData.AddDataWithDataset(ds, true, true, false);
lsvData.SetAlternateColorRow();
//show(lsvData, sql, false);
}
private void GetData()
{
string Connection = "";
if (_BID == 1)
{
Connection = _cBeauty.GetConnectionBB();
_COMMISSION = 1;
}
else if (_BID == 2)
{
Connection = _cBeauty.GetConnectionBC();
_COMMISSION = 2;
}
string STCODE = txtscode.Text;
string WHCODE = txtwhcode.Text;
string sql = @"SELECT AA.WHCODE,AA.ABBNAME,CONVERT(VARCHAR,AA.WORKDATE,103) AS DATE ,AA.ABBNO,AA.STCODE,AA.FULLNAME,PAY_CASH,PAY_CARD1,AA.TOTAL_NET
FROM (
SELECT WHCODE,B.ABBNAME,WORKDATE,ABBNO,C.STCODE,C.FULLNAME,CAST(NET AS decimal(18,2)) AS TOTAL_NET,PAY_CASH,PAY_CARD1
FROM POS_PT A
LEFT JOIN MAS_WH B
ON A.WH_ID = B.ID
LEFT JOIN MAS_ST C
ON A.ST_ID = C.ID
WHERE PTSTATUS IN ('S','R')
AND WORKDATE BETWEEN '" + dtp_StartDate.getDateOnlyForSql() + @"' AND '" + dtp_EndDate.getDateOnlyForSql() + @"'";
sql += " AND ISNULL(PAY_CARD_CD_ID1,0) <> '" + _FP + "'";
sql += @") AA
LEFT JOIN
(
SELECT B.WHCODE,B.ABBNAME AS WHNAME,WORKDATE, ABBNO,
TOTALB4DISC,DISCOUNTAMT,TOTALB4DISC - DISCOUNTAMT AS NET ,CAST(DVAL AS decimal(18, 2)) AS DVAL
FROM POS_PT_PR A
LEFT JOIN MAS_WH B ON A.WH_ID = B.ID
WHERE A.PRCODE = (
SELECT PRCODE FROM [192.168.10.243].[BeautySystem].[dbo].COMMISSION_PRCODE WHERE CFLAG = 0 AND BRAND = '" + _COMMISSION + @"'
)
AND A.DVAL IN (
SELECT DVAL FROM [192.168.10.243].[BeautySystem].[dbo].COMMISSION_RATE WHERE CFLAG = 0 AND BRAND = '" + _COMMISSION + @"'
)
AND WORKDATE BETWEEN '" + dtp_StartDate.getDateOnlyForSql() + @"' AND '" + dtp_EndDate.getDateOnlyForSql() + @"'
) BB
ON AA.WHCODE = BB.WHCODE AND AA.WORKDATE = BB.WORKDATE AND AA.ABBNO = BB.ABBNO
WHERE BB.WHCODE IS NULL AND BB.WORKDATE IS NULL AND BB.ABBNO IS NULL " ;
if (txtscode.Text != "")
{
sql += " AND AA.STCODE = '" + STCODE + "'";
}
if (txtwhcode.Text != "")
{
sql += " AND AA.WHCODE = '" + WHCODE + "'";
}
sql += " ORDER BY AA.WORKDATE ,AA.WHCODE";
DataSet ds = cData.getDataSetWithSqlCommand(Connection, sql, 1000, true);
if (ds.Tables[0].Rows.Count <= 0)
{
cMessage.Error_NoData();
return;
}
lsvData.AddDataWithDataset(ds, true, true, false);
lsvData.SetAlternateColorRow();
//show(lsvData, sql, false);
}
private void btnSumbit_Click(object sender, EventArgs e)
{
if (txtDVAL.Text.Length <= 0)
{
cMessage.Error_InvalidData();
return;
}
frmLogin frm = new frmLogin(_cBeauty, "FA");
frm.ShowDialog();
if (_cBeauty._STCODE_LOG == null || _cBeauty._STCODE_LOG == "" || _cBeauty._DPCODE_LOG == null || _cBeauty._DPCODE_LOG == "" ||
_cBeauty._STNAME_LOG == null || _cBeauty._STNAME_LOG == "")
{
cMessage.Error_InvalidData();
return;
}
else
{
TYPE = cmbTYID.SelectedValue.ToString();
if (TYPE == "1")
{
TYPE_NEW = "หน้าร้าน";
}
else if (TYPE == "2")
{
TYPE_NEW = "ออนไลน์";
}
else if (TYPE == "3")
{
TYPE_NEW = "ขายส่ง";
}
else if (TYPE == "4")
{
TYPE_NEW = "Foodpanda";
}
if (_chack == "IS")
{
BID = cmbBID.SelectedValue.ToString();
if (BID == "1")
{
BID_NEW = "BB";
con_wh = _cBeauty.GetConnectionBB();
}
else if (BID == "2")
{
BID_NEW = "BC";
con_wh = _cBeauty.GetConnectionBC();
}
}
if (_chack == "IS")
{
SaveData_IS();
}
else if (_chack == "UP")
{
SaveData_UP();
}
this.Close();
}
}