public int[] SelectCostSlipNo(DateTime reportDate, string taskCode, string officeCode)
{
int[] sNoDArray = new int[] { -1 };
string sqlStr = " DISTINCT SlipNo FROM D_CostReport WHERE ReportDate = '" + reportDate + "' AND OfficeCode = '" + officeCode + "'";
if (taskCode != "")
{
sqlStr += " AND TaskCode = '" + taskCode + "'";
}
sqlStr += " ORDER BY SlipNo";
SqlHandling sh = new SqlHandling();
DataTable dt = sh.SelectFull_Core(sqlStr);
if (dt == null || dt.Rows.Count < 1)
{
return(sNoDArray);
}
sNoDArray = new int[dt.Rows.Count];
DataRow dr;
for (int i = 0; i < dt.Rows.Count; i++)
{
dr = dt.Rows[i];
sNoDArray[i] = Convert.ToInt32(dr["SlipNo"]);
}
return(sNoDArray);
}
public DataTable SelectPayment(DateTime reportDate, string officeCode, string department)
{
string sqlStr = " * FROM D_OsPayment "
+ "WHERE OfficeCode = '" + officeCode + "' AND Department = '" + department + "' AND ReportDate = '" + reportDate + "'";
SqlHandling sh = new SqlHandling();
DataTable dt = sh.SelectFull_Core(sqlStr); ;
if (dt == null || dt.Rows.Count < 1) return null;
return dt;
}
public CostReportData[] SelectCostReportItemCode(string officeCode, string taskCode, int fYear)
{
//string sqlStr = " DISTINCT LEFT(ItemCode,4) AS iCod, Item, MemberCode FROM D_CostReport WHERE OfficeCode = '" + officeCode + "' AND TaskCode = '" + taskCode + "' AND (ItemCode LIKE 'A%' OR ItemCode LIKE 'B%')"
// + " AND ( ReportDate BETWEEN '" + DHandling.FisicalYearStartDate( fYear ) + "' AND '" + DHandling.FisicalYearEndDate( fYear ) + "') ORDER BY LEFT(ItemCode,4)";
//string sqlStr = " ItemCode, Item, MemberCode FROM D_CostReport WHERE OfficeCode = '" + officeCode + "' AND TaskCode = '" + taskCode + "' AND (ItemCode LIKE 'A%' OR ItemCode LIKE 'B%')"
// + " AND ( ReportDate BETWEEN '" + DHandling.FisicalYearStartDate( fYear ) + "' AND '" + DHandling.FisicalYearEndDate( fYear ) + "') ORDER BY SUBSTRING LEFT(ItemCode,4)";
string sqlStr = " ItemCode, Item, MemberCode FROM D_CostReport WHERE OfficeCode = '" + officeCode + "' AND TaskCode = '" + taskCode + "' AND (ItemCode LIKE 'A%' OR ItemCode LIKE 'B%')"
+ " AND ( ReportDate BETWEEN '" + DHandling.FisicalYearStartDate(fYear) + "' AND '" + DHandling.FisicalYearEndDate(fYear) + "') ORDER BY ItemCode";
SqlHandling sh = new SqlHandling();
DataTable dt = sh.SelectFull_Core(sqlStr);
if (dt == null)
{
return(null);
}
string wkItemCode = "";
int rowIdx = 0;
DataRow dr;
for (int i = 0; i < dt.Rows.Count; i++)
{
dr = dt.Rows[i];
if (wkItemCode != (Convert.ToString(dr["ItemCode"])).TrimEnd())
{
rowIdx++;
wkItemCode = (Convert.ToString(dr["ItemCode"])).TrimEnd();
}
}
CostReportData[] crd = new CostReportData[rowIdx];
wkItemCode = "";
rowIdx = 0;
for (int i = 0; i < dt.Rows.Count; i++)
{
dr = dt.Rows[i];
if (wkItemCode != (Convert.ToString(dr["ItemCode"])).TrimEnd())
{
wkItemCode = (Convert.ToString(dr["ItemCode"])).TrimEnd();
crd[rowIdx] = new CostReportData();
crd[rowIdx].ItemCode = wkItemCode;
crd[rowIdx].Item = Convert.ToString(dr["Item"]);
rowIdx++;
}
}
return(crd);
}
//--------------------------------------------------------//
// Method
//--------------------------------------------------------//
public DataTable SelectOsWkReport(string partnerCode, string taskCode, DateTime reportMM)
{
SqlHandling sh = new SqlHandling();
DataTable dt = sh.SelectFull_Core("DISTINCT ReportDate, ContractForm FROM D_OsWkReport WHERE PartnerCode = '" + partnerCode + "' AND TaskCode = '" + taskCode
+ "' AND ( ReportDate BETWEEN '" + reportMM.BeginOfMonth() + "' AND '" + reportMM.EndOfMonth() + "') ORDER BY ReportDate");
if (dt == null || dt.Rows.Count < 1)
{
return(null);
}
return(dt);
}
public DataTable SelectOsWkReportPartnerCode(DateTime dateFr, string officeCode, string department)
{
DateTime dateTo = DHandling.EndOfMonth(dateFr);
SqlHandling sh = new SqlHandling();
DataTable dt = sh.SelectFull_Core("DISTINCT PartnerCode FROM D_OsWkReport WHERE OfficeCode = '" + officeCode + "' AND Department = '" + department
+ "' AND ( ReportDate BETWEEN '" + dateFr + "' AND '" + dateTo + "') ORDER BY PartnerCode");
if (dt == null || dt.Rows.Count < 1)
{
return(null);
}
return(dt);
}
public DataTable SelectPayOff(DateTime reportDate, string officeCode, string department, string costCode)
{
string sqlStr = " * FROM D_OsPayOff "
+ "WHERE OfficeCode = '" + officeCode + "' AND Department = '" + department + "' AND ItemCode = '" + costCode + "' AND ReportDate = '" + reportDate + "'";
SqlHandling sh = new SqlHandling();
DataTable dt = sh.SelectFull_Core(sqlStr);;
if (dt == null || dt.Rows.Count < 1)
{
return(null);
}
return(dt);
}
public int CountSlipNo(DateTime dateFr, DateTime dateTo, string taskCode, string officeCode, string department)
{
string sqlStr = " COUNT(DISTINCT SlipNo) AS sNC FROM D_CostReport WHERE OfficeCode = '" + officeCode + "' AND Department = '" + department + "' AND TaskCode = '" + taskCode
+ "' AND ( ReportDate BETWEEN '" + dateFr.StripTime() + "' AND '" + dateTo.StripTime() + "')";
SqlHandling sh = new SqlHandling();
DataTable dt = sh.SelectFull_Core(sqlStr);
if (dt == null || dt.Rows.Count < 1)
{
return(0);
}
DataRow dr = dt.Rows[0];
return(Convert.ToInt32(dr["sNC"]));
}
public int SelectOsWkReportID(int slipNo)
{
string sqlStr = " DISTINCT OsWkReportID FROM D_OsWkDetail WHERE SlipNo = " + slipNo;
SqlHandling sh = new SqlHandling();
DataTable dt = sh.SelectFull_Core(sqlStr);
if (dt == null || dt.Rows.Count < 1)
{
return(0);
}
DataRow dr = dt.Rows[0];
return(Convert.ToInt32(dr["OsWkReportID"]));
}
public int SelectCostSlipNo(DateTime reportDate, string taskCode, string subCoCode, string officeCode, string department)
{
string sqlStr = " DISTINCT SlipNo FROM D_CostReport WHERE ReportDate = '" + reportDate + "' AND TaskCode = '" + taskCode
+ "' AND OfficeCode = '" + officeCode + "' AND Department = '" + department + "' AND SubCoCode = '" + subCoCode + "'";
SqlHandling sh = new SqlHandling();
DataTable dt = sh.SelectFull_Core(sqlStr);
if (dt == null || dt.Rows.Count < 1)
{
return(0);
}
DataRow dr = dt.Rows[0];
return(Convert.ToInt32(dr["SlipNo"]));
}
public Decimal SumMonthlyQuantity(string officeCode, string itemCode, DateTime month)
{
string sqlStr = "SUM( Quantity ) AS SumQty FROM D_CostReport "
+ "WHERE OfficeCode = '" + officeCode + "' AND "
+ "ItemCode = '" + itemCode + "' AND "
+ "( ReportDate BETWEEN '" + month + "' AND '" + month.EndOfMonth() + "' )";
SqlHandling sh = new SqlHandling();
DataTable dt = sh.SelectFull_Core(sqlStr);
if (dt == null)
{
return(0);
}
CostReportData crd = new CostReportData();
DataRow dr = dt.Rows[0];
return((string.IsNullOrEmpty(Convert.ToString(dr["SumQty"]))) ? 0 : Convert.ToDecimal(dr["SumQty"]));
}
public Decimal SumMonthlyCost(string taskCode, string officeCode, DateTime month)
{
string sqlStr = "SUM( Cost ) AS SumCost FROM D_CostReport "
+ "WHERE TaskCode = '" + taskCode + "' AND "
+ "OfficeCode = '" + officeCode + "' AND "
+ "( LEFT(ItemCode, 1 ) = 'A' OR LEFT( ItemCode, 1 ) = 'B' ) AND "
+ "( ReportDate BETWEEN '" + month + "' AND '" + month.EndOfMonth() + "' )";
SqlHandling sh = new SqlHandling();
DataTable dt = sh.SelectFull_Core(sqlStr);
if (dt == null)
{
return(0);
}
CostReportData crd = new CostReportData();
DataRow dr = dt.Rows[0];
return((string.IsNullOrEmpty(Convert.ToString(dr["SumCost"]))) ? 0 : Convert.ToDecimal(dr["SumCost"]));
}