public TaskCodeNameData[] SelectTaskCodeNameFromOsWkReport(string pCode, DateTime dateFr)
{
DateTime dateTo = DHandling.EndOfMonth(dateFr);
string selParam = "DISTINCT WR.TaskCode AS TaskCode, TI.TaskName AS TaskName, TI.LeaderMCode AS LeaderMCode FROM "
+ "D_TaskInd TI INNER JOIN D_OsWkReport WR ON TI.TaskCode = WR.TaskCode "
+ "WHERE (WR.ReportDate BETWEEN '" + dateFr + "' AND '" + dateTo + "') AND WR.PartnerCode = '" + pCode + "' ORDER BY WR.TaskCode";
SqlHandling sh = new SqlHandling();
DataTable dt = sh.SelectFullDescription(selParam);
if (dt == null || dt.Rows.Count < 1)
{
return(null);
}
TaskCodeNameData[] tcd = new TaskCodeNameData[dt.Rows.Count];
DataRow dr;
for (int i = 0; i < dt.Rows.Count; i++)
{
dr = dt.Rows[i];
tcd[i] = new TaskCodeNameData();
tcd[i].TaskCode = Convert.ToString(dr["TaskCode"]);
tcd[i].TaskName = Convert.ToString(dr["TaskName"]);
tcd[i].LeaderMCode = Convert.ToString(dr["LeaderMCode"]);
}
return(tcd);
}
public bool VersionDistinct(string table, string param)
{
SqlHandling sh = new SqlHandling(table);
DataTable dt = sh.SelectFullDescription("DISTINCT VersionNo FROM " + table + " " + param);
if (dt == null)
{
emptyComboBox();
return(Basic());
}
//DataRow dr;
//valItem = new string[dt.Rows.Count];
//dispItem = new string[dt.Rows.Count];
//for( int i = 0; i < dt.Rows.Count; i++ )
//{
// dr = dt.Rows[i];
// valItem[i] = Convert.ToString( i );
// dispItem[i] = Convert.ToString( dr["VersionNo"] );
//}
if (SetValueDt(dt, "VersionNo") == false)
{
return(false);
}
return(Basic());
}
public decimal[] SelectSummaryWorkReport(string memberCode, DateTime date)
{
//decimal[] workHour = new decimal[2];
decimal[] workHour = new decimal[3];
SqlHandling sh = new SqlHandling();
DataTable dt = sh.SelectFullDescription(" SUM(MemberNH) AS NHours,SUM(MemberOH) AS OHours, SUM(UserCheck) AS UCheck FROM D_WorkReport WHERE MemberCode = '" + memberCode + "' AND ReportDate = '" + date.StripTime() + "'");
//DataTable dt = sh.SelectFullDescription(" SUM(MemberNH) AS NHours,SUM(MemberOH) AS OHours FROM D_WorkReport WHERE MemberCode = '" + memberCode + "' AND ReportDate = '" + date.StripTime() + "'");
if (dt == null || dt.Rows.Count == 0)
{
workHour[0] = -1;
workHour[1] = -1;
}
else
{
DataRow dr = dt.Rows[0];
if (Convert.ToString(dr["NHours"]) == "")
{
workHour[0] = -1;
workHour[1] = -1;
}
else
{
workHour[0] = Convert.ToDecimal(dr["NHours"]);
workHour[1] = Convert.ToDecimal(dr["OHours"]);
}
// 20161115 append
workHour[2] = (Convert.ToString(dr["UCheck"]) == "") ? 0 : Convert.ToDecimal(dr["UCheck"]);
}
return(workHour);
}
public VolumeData[] SelectVolumeData(string officeCode, string department, string taskCode, int year, int preYear)
{
string sqlDepartment = "";
if (!String.IsNullOrEmpty(department))
{
sqlDepartment = " AND Department =" + "'" + department + "'";
}
string strSql = " NULL AS TaskCode, NULL AS YearMonth, NULL AS Publisher, ClaimDate, PaidDate, NULL AS Comment, NULL AS TaskStat, NULL AS CarryOverPlanned, NULL AS OfficeCode, NULL AS Department, NULL AS Note, "
+ "Volume AS MonthlyVolume, VolUncomp, VolClaimRem, VolClaim, VolPaid, Claim AS MonthlyClaim, Cost AS MonthlyCost, BalanceClaim, BalanceIncom, Deposit1, Deposit2 FROM D_YearVolume "
+ "WHERE TaskCode = " + "'" + taskCode + "' "
+ "AND OfficeCode =" + "'" + officeCode + "' "
+ sqlDepartment
+ " AND YearMonth = " + "'" + preYear + "'";
SqlHandling sh = new SqlHandling("D_YearVolume");
DataTable dt = sh.SelectFullDescription(strSql);
if (dt == null)
{
return(null);
}
VolumeData[] vd = new VolumeData[dt.Rows.Count];
for (int i = 0; i < dt.Rows.Count; i++)
{
vd[i] = new VolumeData(dt.Rows[i]);
}
return(vd);
}
public string[] SelectCompleteTascCode(string officeCode, string departCode, string ymRange)
{
string strSql = " DISTINCT TaskCode FROM D_Volume WHERE TaskStat = 3 ";
if (!string.IsNullOrEmpty(officeCode))
{
strSql += "AND OfficeCode = '" + officeCode + "' ";
}
if (!string.IsNullOrEmpty(departCode))
{
strSql += "AND Department = '" + departCode + "' ";
}
if (!string.IsNullOrEmpty(ymRange))
{
strSql += "AND YearMonth " + ymRange;
}
strSql += "ORDER BY TaskCode";
SqlHandling sh = new SqlHandling();
DataTable dt = sh.SelectFullDescription(strSql);
if (dt == null || dt.Rows.Count < 1)
{
return(null);
}
string[] completeTask = new string[dt.Rows.Count];
DataRow dr;
for (int i = 0; i < dt.Rows.Count; i++)
{
dr = dt.Rows[i];
completeTask[i] = Convert.ToString(dr["TaskCode"]);
}
return(completeTask);
}
public decimal SelectOAmountPayment(string iCod, string tCod, string oCod, string dept, DateTime rDat)
{
decimal orderAmount;
SqlHandling sh = new SqlHandling();
DataTable dt = sh.SelectFullDescription(" * FROM D_OsPayment WHERE ItemCode = '" + iCod + "' AND TaskCode = '" + tCod + "' AND OfficeCode = '" + oCod
+ "' AND Department = '" + dept + "' AND ReportDate < '" + rDat.StripTime() + "' ORDER BY ReportDate DESC");
if (dt == null || dt.Rows.Count == 0) return 0;
DataRow dr = dt.Rows[0];
orderAmount = (Convert.ToString(dr["OrderAmount"]) == "") ? 0 : Convert.ToDecimal(dr["OrderAmount"]);
return orderAmount;
}
public DataTable SelectFull_Core(string sqlStr)
{
SqlHandling sh = new SqlHandling();
DataTable dt = sh.SelectFullDescription(sqlStr);
if (dt == null)
{
return(null);
}
//if ( dt.Rows.Count == 0 ) return null;
return(dt);
}
public decimal SelectSumAmountPayment(string iCod, string tCod, string oCod, string dept, DateTime rDat)
{
decimal Kei;
SqlHandling sh = new SqlHandling();
DataTable dt = sh.SelectFullDescription(" SUM(Amount) AS Kei FROM D_OsPayment WHERE ItemCode = '" + iCod + "' AND TaskCode = '" + tCod + "' AND OfficeCode = '" + oCod
+ "' AND Department = '" + dept + "' AND ReportDate < '" + rDat + "'");
if (dt == null || dt.Rows.Count == 0) return 0;
DataRow dr = dt.Rows[0];
Kei = (Convert.ToString(dr["Kei"]) == "") ? 0 : Convert.ToDecimal(dr["Kei"]);
return Kei;
}
private int editTaskIndDataItem(string sqlStr, string retVal)
{
SqlHandling sh = new SqlHandling();
DataTable dt = sh.SelectFullDescription(sqlStr);
if (dt == null || dt.Rows.Count < 1)
{
return(-1);
}
DataRow dr = dt.Rows[0];
return(Convert.ToInt32(dr[retVal]));
}
public YearVolumeData SelectSummaryYearVolume(string officeCode, string department, int yymm)
{
SqlHandling sh = new SqlHandling();
DataTable dt = sh.SelectFullDescription(sumPara + " OfficeCode = '" + officeCode + "' AND Department = '" + department + "' AND YearMonth = " + yymm);
if (dt.Rows.Count == 0)
{
return(null);
}
DataRow dr = dt.Rows[0];
YearVolumeData yvold = setYearVolumeData(dr);
yvold.OfficeCode = officeCode;
yvold.Department = department;
return(yvold);
}
public bool TableDataForCostData(string dItem, string strSql)
{
SqlHandling sh = new SqlHandling();
DataTable dt = sh.SelectFullDescription(strSql);
if (dt == null)
{
return(false);
}
if (!SetValueDt(dt, dItem))
{
return(false);
}
return(Basic());
}
//--------------------------------------------------------//
// Method
//--------------------------------------------------------//
public string SelectMemberName(string mCode)
{
SqlHandling sh = new SqlHandling();
DataTable dt = sh.SelectFullDescription(" Name FROM M_Members WHERE MemberCode = '" + mCode + "'");
if (dt == null)
{
return("");
}
if (dt.Rows.Count == 0)
{
return("");
}
DataRow dr = dt.Rows[0];
return(Convert.ToString(dr["Name"]));
}
public VolumeData[] SelectVolumeDate(string strSql)
{
SqlHandling sh = new SqlHandling("D_Volume");
DataTable dt = sh.SelectFullDescription(strSql);
if (dt == null || dt.Rows.Count < 1)
{
return(null);
}
VolumeData[] vd = new VolumeData[dt.Rows.Count];
for (int i = 0; i < dt.Rows.Count; i++)
{
vd[i] = new VolumeData(dt.Rows[i]);
}
return(vd);
}
public VolumeData SelectSummaryVolume(string officeCode, string department, string ymPara)
{
VolumeData vold = new VolumeData();
SqlHandling sh = new SqlHandling();
DataTable dt = sh.SelectFullDescription(sumPara + " OfficeCode = '" + officeCode + "' AND Department = '" + department + "' AND YearMonth " + ymPara);
//DataTable dt = sh.SelectFullDescription( sumPara + " OfficeCode = '" + officeCode + "' AND Department = '" + department + "' AND YearMonth " + ymPara + " AND TaskStat != 3" ); // 休止以外
if (dt.Rows.Count == 0)
{
return(null);
}
DataRow dr = dt.Rows[0];
vold = setVolumeData(dr);
vold.OfficeCode = officeCode;
vold.Department = department;
return(vold);
}
public VolumeData SelectSummaryVolume(string officeCode, string department, int yymm)
{
VolumeData vold = new VolumeData();
SqlHandling sh = new SqlHandling();
DataTable dt = sh.SelectFullDescription(sumPara + " OfficeCode = '" + officeCode + "' AND Department = '" + department + "' AND YearMonth = " + yymm);
//if (dt == null) return vold;
if (dt.Rows.Count == 0)
{
return(null);
}
DataRow dr = dt.Rows[0];
vold = setVolumeData(dr);
vold.OfficeCode = officeCode;
vold.Department = department;
vold.YearMonth = yymm;
return(vold);
}
public string[] SelectMembersOffice(string mCode)
{
SqlHandling sh = new SqlHandling("M_Members");
DataTable dt = sh.SelectFullDescription(" OfficeCode, Department FROM M_Members WHERE MemberCode = '" + mCode + "'");
if (dt == null)
{
return(null);
}
if (dt.Rows.Count == 0)
{
return(null);
}
string[] codeArray = new string[2];
DataRow dr = dt.Rows[0];
codeArray[0] = Convert.ToString(dr["OfficeCode"]);
codeArray[1] = Convert.ToString(dr["Department"]);
return(codeArray);
}
public TaskCodeNameData[] SelectTaskCodeNameFromCostReport(DateTime dateFr, DateTime dateTo, string scCode, string officeCode)
{
string selParam = "DISTINCT TaskCode FROM D_CostReport WHERE ( ReportDate BETWEEN '" + dateFr + "' AND '" + dateTo + "') "
+ "AND OfficeCode = '" + officeCode + "' AND ";
selParam += scCode[0] == 'F' ? "SubCoCode = " : "ItemCode = ";
selParam += "'" + scCode + "' ORDER BY TaskCode";
SqlHandling sh = new SqlHandling();
DataTable dt = sh.SelectFullDescription(selParam);
if (dt == null || dt.Rows.Count < 1)
{
return(null);
}
string[] taskCdArray = new string[dt.Rows.Count];
DataRow dr;
for (int i = 0; i < dt.Rows.Count; i++)
{
dr = dt.Rows[i];
taskCdArray[i] = Convert.ToString(dr["TaskCode"]);
}
StringUtility utl = new StringUtility();
TaskIndData tid = new TaskIndData();
TaskCodeNameData[] tcd = new TaskCodeNameData[taskCdArray.Length];
for (int i = 0; i < taskCdArray.Length; i++)
{
tcd[i] = new TaskCodeNameData();
tcd[i].TaskCode = taskCdArray[i];
tid = tid.SelectTaskIndData(utl.SubstringByte(tcd[i].TaskCode, 2, 3) == "999"
? utl.SubstringByte(tcd[i].TaskCode, 0, 1) + Convert.ToString(DHandling.FisicalYear() - 2000) + utl.SubstringByte(tcd[i].TaskCode, 1, 1) + "999"
: tcd[i].TaskCode);
tcd[i].TaskName = tid.TaskName;
tcd[i].LeaderMCode = tid.LeaderMCode;
}
return(tcd);
}
public CostData[] SelectCostDataJoinOsWkReport(string officeCode)
{
SqlHandling sh = new SqlHandling();
DataTable dt = sh.SelectFullDescription("DISTINCT WR.PartnerCode AS CostCode, MC.CostID AS CostID, MC.Item AS Item, "
+ "MC.ItemDetail AS ItemDetail, MC.Unit AS Unit, MC.Cost AS Cost, "
+ "MC.OfficeCode AS OfficeCode, MC.MemberCode AS MemberCode "
+ "FROM M_Cost MC INNER JOIN D_OsWkReport WR ON MC.CostCode = WR.PartnerCode "
+ "WHERE MC.OfficeCode = '" + officeCode + "' AND "
+ "WR.PartnerCode BETWEEN 'F001' AND 'F998' ORDER BY WR.PartnerCode");
if (dt == null || dt.Rows.Count < 1)
{
return(null);
}
CostData[] cmd = new CostData[dt.Rows.Count];
for (int i = 0; i < dt.Rows.Count; i++)
{
cmd[i] = new CostData(dt.Rows[i]);
}
return(cmd);
}
public CostData[] SelectCostDataFromCostReport(DateTime dateFr, DateTime dateTo, string officeCode, string department)
{
SqlHandling sh = new SqlHandling();
DataTable dt = sh.SelectFullDescription("DISTINCT ItemCode FROM D_CostReport WHERE (ReportDate BETWEEN '" + dateFr + "' AND '" + dateTo
+ "') AND OfficeCode = '" + officeCode + "' AND Department = '" + department
+ "' AND ItemCode LIKE 'A%' ORDER BY ItemCode");
string[] itemArray = new string[1];
int itemCnt = 0;
if (dt != null && dt.Rows.Count > 0)
{
itemArray = new string[dt.Rows.Count];
itemCnt = dt.Rows.Count;
DataRow dr;
for (int i = 0; i < dt.Rows.Count; i++)
{
dr = dt.Rows[i];
itemArray[i] = Convert.ToString(dr["ItemCode"]);
}
}
dt = sh.SelectFullDescription("DISTINCT SubCoCode FROM D_CostReport WHERE (ReportDate BETWEEN '" + dateFr + "' AND '" + dateTo
+ "') AND OfficeCode = '" + officeCode + "' AND Department = '" + department
+ "' AND SubCoCode BETWEEN 'F001' AND 'F998' ORDER BY SubCoCode");
string[] subCoArray = new string[1];
int subCoCnt = 0;
if (dt != null && dt.Rows.Count > 0)
{
subCoArray = new string[dt.Rows.Count];
subCoCnt = dt.Rows.Count;
DataRow dr;
for (int i = 0; i < dt.Rows.Count; i++)
{
dr = dt.Rows[i];
subCoArray[i] = Convert.ToString(dr["SubCoCode"]);
}
}
if ((itemCnt + subCoCnt) == 0)
{
return(null);
}
string[] costCodeArray = new string[itemCnt + subCoCnt];
if (itemCnt == 0)
{
Array.Copy(subCoArray, costCodeArray, subCoCnt);
}
else
{
Array.Copy(itemArray, costCodeArray, itemCnt);
if (subCoCnt != 0)
{
Array.Copy(subCoArray, 0, costCodeArray, itemCnt, subCoCnt);
}
}
CostData cd = new CostData();
CostData[] cmd = new CostData[costCodeArray.Length];
for (int i = 0; i < costCodeArray.Length; i++)
{
cmd[i] = new CostData();
cmd[i].CostCode = costCodeArray[i];
cmd[i].Item = cd.SelectCostName(cmd[i].CostCode);
}
return(cmd);
}