public void JobProcess_AddNew_Save(string info)
{
JObject job = (JObject)JsonConvert.DeserializeObject(HttpUtility.UrlDecode(Server.UrlDecode(info)));
int HouseId = SafeValue.SafeInt(job["HouseId"], 0);
string JobNo = SafeValue.SafeString(job["JobNo"]);
string LotNo = SafeValue.SafeString(job["LotNo"]);
string ProcessStatus = SafeValue.SafeString(job["ProcessStatus"]);
int Qty = SafeValue.SafeInt(job["Qty"], 0);
string DatePlan = SafeValue.SafeString(job["DatePlan"]);
string ProcessType = SafeValue.SafeString(job["ProcessType"]);
string LocationCode = SafeValue.SafeString(job["LocationCode"]);
string Remark1 = SafeValue.SafeString(job["Remark1"]);
string Remark2 = SafeValue.SafeString(job["Remark2"]);
List <ConnectSql_mb.cmdParameters> list = new List <ConnectSql_mb.cmdParameters>();
list.Add(new ConnectSql_mb.cmdParameters("@HouseId", HouseId, SqlDbType.Int));
list.Add(new ConnectSql_mb.cmdParameters("@JobNo", JobNo, SqlDbType.NVarChar, 100));
list.Add(new ConnectSql_mb.cmdParameters("@LotNo", LotNo, SqlDbType.NVarChar, 100));
list.Add(new ConnectSql_mb.cmdParameters("@ProcessStatus", ProcessStatus, SqlDbType.NVarChar, 30));
list.Add(new ConnectSql_mb.cmdParameters("@Qty", Qty, SqlDbType.Int));
list.Add(new ConnectSql_mb.cmdParameters("@DatePlan", DatePlan, SqlDbType.NVarChar, 10));
list.Add(new ConnectSql_mb.cmdParameters("@ProcessType", ProcessType, SqlDbType.NVarChar, 50));
list.Add(new ConnectSql_mb.cmdParameters("@LocationCode", LocationCode, SqlDbType.NVarChar, 100));
list.Add(new ConnectSql_mb.cmdParameters("@Remark1", Remark1, SqlDbType.NVarChar, 100));
list.Add(new ConnectSql_mb.cmdParameters("@Remark2", Remark2, SqlDbType.NVarChar, 100));
string sql = string.Format(@"insert into job_process (HouseId,JobNo,LotNo,ProcessStatus,Qty,DateEntry,DatePlan,DateProcess,DateInspect,ProcessType,LocationCode,Remark1,Remark2)
values(@HouseId,@JobNo,@LotNo,@ProcessStatus,@Qty,getdate(),@DatePlan,@DatePlan,@DatePlan,@ProcessType,@LocationCode,@Remark1,@Remark2)");
bool status = ConnectSql_mb.ExecuteNonQuery(sql, list).status;
Common.WriteJsonP(status, Common.StringToJson(""));
}
public BizResult jobBilling(string user)
{
BizResult res = new BizResult();
if (this.job != null && this.job.Id > 0)
{
string sql = string.Format(@"select * from CTM_JobDet2 as det2
left outer join ctm_job as job on det2.JobNo=job.JobNo
where job.Id=@jobId and det2.Statuscode<>'C'");
List <ConnectSql_mb.cmdParameters> list = new List <ConnectSql_mb.cmdParameters>();
list.Add(new ConnectSql_mb.cmdParameters("@jobId", this.job.Id, SqlDbType.Int));
DataTable dt = ConnectSql_mb.GetDataTable(sql, list);
if (dt.Rows.Count > 0)
{
res.context = "Have pending trips need to delivery";
}
else
{
sql = "update CTM_Job set StatusCode='CLS',JobStatus='Completed' where Id=@jobId";
if (ConnectSql_mb.ExecuteNonQuery(sql, list).status)
{
res.status = true;
C2.CtmJobEventLog elog = new C2.CtmJobEventLog();
elog.Platform_isWeb();
elog.Controller = user;
elog.ActionLevel_isJOB(this.job.Id);
elog.setActionLevel(this.job.Id, CtmJobEventLogRemark.Level.Job, 7);
elog.log();
}
}
}
else
{
res.context = "error: data empty";
}
return(res);
}
public void Crane_Calendar_GetDataList(string info)
{
string info_ = HttpUtility.UrlDecode(Server.UrlDecode(info));
JObject job = (JObject)JsonConvert.DeserializeObject(info_);
List <ConnectSql_mb.cmdParameters> list = null;
ConnectSql_mb.cmdParameters cpar = null;
string status = "1";
string context = Common.StringToJson("");
string sql_where = "";
if (!job["role"].ToString().ToLower().Equals("admin"))
{
sql_where = " and det2.DriverCode=@DriverCode";
}
string sql = string.Format(@"select det2.Id,det2.JobNo,det2.ContainerNo,DriverCode,TowheadCode,ChessisCode,det2.Statuscode,det2.Remark,det2.RequestVehicleType,
FromCode,FromDate,FromTime,ToCode,ToDate,ToTime,TripCode as JobType,det2.DoubleMounting,det2.BookingDate,det2.BookingTime,det2.BookingTime2,v.ContractNo,det2.ByUser,
case det2.Statuscode when 'S' then 1 when 'C' then 3 when 'X' then 4 else 2 end as OrderIndex,
job.IsTrucking,job.IsWarehouse,job.IsLocal,job.IsAdhoc
From CTM_JobDet2 as det2
left outer join ref_Vehicle as v on det2.TowheadCode=v.VehicleCode
left outer join CTM_Job as job on job.JobNo=det2.JobNo
where (DATEDIFF(day,BookingDate,@date)=0 ) and det2.TripCode='CRA' {0}
order by TowheadCode,convert(nvarchar(10), det2.BookingDate,120),det2.BookingTime desc", sql_where);
list = new List <ConnectSql_mb.cmdParameters>();
cpar = new ConnectSql_mb.cmdParameters("@date", job["date"], SqlDbType.NVarChar, 20);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@DriverCode", job["user"], SqlDbType.NVarChar, 100);
list.Add(cpar);
DataTable dt = ConnectSql_mb.GetDataTable(sql, list);
context = Common.DataTableToJson(dt);
//context = Common.StringToJson(ConnectSql_mb.strcon);
Common.WriteJsonP(status, context);
}
public void Controller_Calendar_TrailerStatus_GetDataList(string info)
{
JObject job = (JObject)JsonConvert.DeserializeObject(HttpUtility.UrlDecode(Server.UrlDecode(info)));
string context = "";
List <ConnectSql_mb.cmdParameters> list = new List <ConnectSql_mb.cmdParameters>();
string sql = string.Format(@"select Mast.Code as ChessisCode,Mast.Remark as Size,temp.Id,temp.ContainerNo,temp.ContainerType,temp.DriverCode,temp.FromCode,temp.ToCode,temp.TripCode,temp.FromTime,temp.ToTime,temp.Statuscode
from CTM_MastData as Mast
left outer join (
select det2.Id,det2.ContainerNo,det1.ContainerType,det2.ChessisCode,det2.DriverCode,det2.FromCode,det2.ToCode,det2.TripCode,det2.FromTime,det2.ToTime,det2.Statuscode
from CTM_JobDet2 as det2
left outer join CTM_JobDet1 as det1 on det2.Det1Id=det1.Id
where det2.Id in(
select MAX(Id) from(
select det2.Id,det2MaxTime.ChessisCode
from (
select det2.ChessisCode,MAX(FromTime) as MaxTime
from CTM_JobDet2 as det2
left outer join CTM_JobDet1 as det1 on det2.Det1Id=det1.Id
where datediff(d,@date,det2.FromDate )=0 and ISNULL(det2.ChessisCode,'')<>''
group by det2.ChessisCode
) as det2MaxTime
left outer join CTM_JobDet2 as det2 on det2.ChessisCode=det2MaxTime.ChessisCode and det2.FromTime=det2MaxTime.MaxTime
left outer join CTM_JobDet1 as det1 on det2.det1Id=det1.Id
left outer join CTM_Job as job on det2.JobNo=job.JobNo
where DATEDIFF(d,@date,det2.FromDate )=0 and job.StatusCode<>'CNL'
) as temp group by ChessisCode)
) as temp on temp.ChessisCode=Mast.Code
where Mast.Type='chessis'
order by Id desc,ChessisCode");
list.Add(new ConnectSql_mb.cmdParameters("@date", job["date"], SqlDbType.NVarChar, 10));
DataTable dt = ConnectSql_mb.GetDataTable(sql, list);
context = Common.DataTableToJson(dt);
Common.WriteJsonP(true, context);
}
public void ContainerList_Get(string info)
{
JObject job = (JObject)JsonConvert.DeserializeObject(HttpUtility.UrlDecode(Server.UrlDecode(info)));
//bool status = true;
//string context = Common.StringToJson("");
string user = SafeValue.SafeString(job["user"]);
string No = SafeValue.SafeString(job["No"]);
string fromDate = SafeValue.SafeString(job["fromDate"]);
string toDate = SafeValue.SafeString(job["toDate"]);
if (user.Length > 0)
{
string sql = string.Format(@"select cont.Id as contId,cont.ContainerNo,cont.JobNo,job.JobDate
from CTM_JobDet1 as cont
left outer join CTM_Job as job on cont.JobNo = job.JobNo
where job.ClientId = @user and job.JobNo is not null and job.JobDate>=@fromDate and job.JobDate<@toDate");
List <ConnectSql_mb.cmdParameters> list = new List <ConnectSql_mb.cmdParameters>();
list.Add(new ConnectSql_mb.cmdParameters("@user", user, SqlDbType.NVarChar, 100));
list.Add(new ConnectSql_mb.cmdParameters("@fromDate", fromDate, SqlDbType.NVarChar, 8));
list.Add(new ConnectSql_mb.cmdParameters("@toDate", toDate, SqlDbType.NVarChar, 8));
if (No.Length > 0)
{
list.Add(new ConnectSql_mb.cmdParameters("@ContNo", "%" + No + "%", SqlDbType.NVarChar, 100));
sql += " and cont.ContainerNo like @ContNo";
}
sql += " order by job.JobDate desc";
//throw new Exception(sql);
DataTable dt = ConnectSql_mb.GetDataTable(sql, list);
Common.WriteJsonP(true, Common.DataTableToJson(dt));
}
else
{
Common.WriteJsonP(false, "[]");
}
}
protected void grid_Transport_CustomDataCallback(object sender, DevExpress.Web.ASPxGridView.ASPxGridViewCustomDataCallbackEventArgs e)
{
string temp = e.Parameters;
string[] ar = temp.Split('_');
if (ar.Length == 3)
{
if (ar[0] == "UpdateInlineStatus")
{
contCfsStatusChange(sender, e, SafeValue.SafeInt(ar[1], -1), ar[2]);
}
}
if (temp == "OK")
{
if (list.Count > 0)
{
string sql = " ";
List <ConnectSql_mb.cmdParameters> list_cmd = new List <ConnectSql_mb.cmdParameters>();
for (int i = 0; i < list.Count; i++)
{
int _id = list[i].id;
DateTime _date = list[i].date;
string _time = list[i].time;
string sql_part = string.Format(@"update CTM_JobDet2 set WarehouseScheduleDate='{1}',FromTime='{2}',WarehouseStatus='Scheduled' where Id={0}", _id, _date, _time);
sql = sql + sql_part;
}
if (sql.Length > 0)
{
int res = ConnectSql_mb.ExecuteNonQuery(sql);
if (res > 0)
{
e.Result = "Success";
}
}
}
}
}
protected void btn_search_Click(object sender, EventArgs e)
{
// string sql = string.Format(@"select det2.Id,det2.JobNo,det2.ContainerNo,det2.Statuscode,
//FromCode,FromDate,FromTime,ToCode,ToDate,ToTime,TripCode as JobType,det1.SealNo,det1.TTTime,
//(select cast(isnull(sum(isnull(Qty*Price,0)),0) as decimal(16,2)) from job_cost where TripNo=det2.Id and LineType='DP') as Incentive,
//(select cast(isnull(sum(isnull(Qty*Price,0)),0) as decimal(16,2)) from job_cost where TripNo=det2.Id and LineType='CL') as Claim
//From CTM_JobDet2 as det2
//left outer join CTM_JobDet1 as det1 on det2.Det1Id=det1.Id
//where det2.Statuscode='C' and datediff(day,ToDate,@DateFrom)<=0 and datediff(day,ToDate,@DateTo)>=0 and DriverCode=@DriverCode
//order by ToDate");
string sql = string.Format(@"select det2.Id,det2.JobNo,det2.ContainerNo,det2.Statuscode,
FromCode,FromDate,FromTime,ToCode,ToDate,ToTime,TripCode as JobType,det1.SealNo,det1.TTTime,
(select cast(isnull(sum(isnull(Qty*Price,0)),0) as decimal(16,2)) from job_cost as c where TripNo=det2.Id and LineType='DP' and c.DriverCode=det2.DriverCode) as Incentive,
(select cast(isnull(sum(isnull(Qty*Price,0)),0) as decimal(16,2)) from job_cost as c where TripNo=det2.Id and LineType='CL') as Claim
From CTM_JobDet2 as det2
left outer join CTM_JobDet1 as det1 on det2.Det1Id=det1.Id
where det2.Statuscode='C' and datediff(day,FromDate,@DateFrom)<=0 and datediff(day,FromDate,@DateTo)>=0 and DriverCode=@DriverCode
union all
select det2.Id,det2.JobNo,det2.ContainerNo,det2.Statuscode,
FromCode,FromDate,FromTime,ToCode,ToDate,ToTime,TripCode as JobType,det1.SealNo,det1.TTTime,
(select cast(isnull(sum(isnull(Qty*Price,0)),0) as decimal(16,2)) from job_cost as c where TripNo=det2.Id and LineType='DP' and c.DriverCode=det2.DriverCode2) as Incentive,
0 as Claim
From CTM_JobDet2 as det2
left outer join CTM_JobDet1 as det1 on det2.Det1Id=det1.Id
where det2.Statuscode='C' and datediff(day,FromDate,@DateFrom)<=0 and datediff(day,FromDate,@DateTo)>=0 and DriverCode2=@DriverCode
order by FromDate");
List <ConnectSql_mb.cmdParameters> list = new List <ConnectSql_mb.cmdParameters>();
list.Add(new ConnectSql_mb.cmdParameters("@DriverCode", search_driver.Text, SqlDbType.NVarChar, 100));
list.Add(new ConnectSql_mb.cmdParameters("@DateFrom", search_from.Text, SqlDbType.NVarChar, 100));
list.Add(new ConnectSql_mb.cmdParameters("@DateTo", search_to.Text, SqlDbType.NVarChar, 100));
DataTable dt = ConnectSql_mb.GetDataTable(sql, list);
rp.DataSource = dt;
rp.DataBind();
}
public void Vehcile_IssueReport_GetData(string info)
{
string info_ = HttpUtility.UrlDecode(Server.UrlDecode(info));
JObject job = (JObject)JsonConvert.DeserializeObject(info_);
List <ConnectSql_mb.cmdParameters> list = null;
ConnectSql_mb.cmdParameters cpar = null;
string status = "1";
string context = Common.StringToJson("");
string sql = string.Format(@"select * from Vehicle_IssueReport where Id=@Id");
list = new List <ConnectSql_mb.cmdParameters>();
cpar = new ConnectSql_mb.cmdParameters("@Id", job["JobNo"], SqlDbType.Int);
list.Add(cpar);
DataTable dt = ConnectSql_mb.GetDataTable(sql, list);
string detail = Common.DataRowToJson(dt, true);
sql = string.Format(@"select Id,FileType,FileName,FilePath,FileNote,'' as FP500 From CTM_Attachment where JobType='IssueRp' and TripId={0}", job["JobNo"]);
dt = ConnectSql_mb.GetDataTable(sql);
for (int i = 0; i < dt.Rows.Count; i++)
{
string path = dt.Rows[i]["FilePath"].ToString();
if (RebuildImage.Image_ExistOtherSize(path, dt.Rows[i]["FileType"].ToString(), 500))
{
path = path.Substring(0, path.LastIndexOf('/')) + "/" + 500 + path.Substring(path.LastIndexOf('/'));
}
dt.Rows[i]["FP500"] = path;
}
string attachment = Common.DataTableToJson(dt);
context = string.Format(@"{0}detail:{2},attachment:{3}{1}", "{", "}", detail, attachment);
Common.WriteJsonP(status, context);
}
public void EGL_Driver_GetList(string info)
{
JObject job = (JObject)JsonConvert.DeserializeObject(HttpUtility.UrlDecode(Server.UrlDecode(info)));
string status = "1";
string context = Common.StringToJson("");
string role = job["role"].ToString();
string user = job["user"].ToString();
string no = job["no"].ToString();
string sql = string.Format(@"select Id,Code,Name,Tel,ICNo,Remark from ctm_driver where 1=1");
if (no != null && no.Length > 0)
{
sql += " and Code like @no";
}
List <ConnectSql_mb.cmdParameters> list = new List <ConnectSql_mb.cmdParameters>();
list.Add(new ConnectSql_mb.cmdParameters("@no", "%" + no + "%", SqlDbType.NVarChar, 100));
DataTable dt = ConnectSql_mb.GetDataTable(sql, list);
context = Common.DataTableToJson(dt);
Common.WriteJsonP(status, context);
}
public void EGL_MasterData_HrPerson(string info)
{
JObject job = (JObject)JsonConvert.DeserializeObject(HttpUtility.UrlDecode(Server.UrlDecode(info)));
string status = "1";
string context = Common.StringToJson("");
string role = job["role"].ToString().Trim().ToLower();
string user = job["user"].ToString();
string sql_where = "";
if (role != "admin" && role != "hr")
{
sql_where = " where Name=@Name";
}
string sql = string.Format(@"select Id,Name as c from Hr_Person {0} order by Name", sql_where);
List <ConnectSql_mb.cmdParameters> list = new List <ConnectSql_mb.cmdParameters>();
list.Add(new ConnectSql_mb.cmdParameters("@Name", user, SqlDbType.NVarChar, 100));
DataTable dt = ConnectSql_mb.GetDataTable(sql, list);
context = Common.DataTableToJson(dt);
Common.WriteJsonP(status, context);
}
public void Case_GetDataDetail(string info)
{
JObject job = (JObject)JsonConvert.DeserializeObject(HttpUtility.UrlDecode(Server.UrlDecode(info)));
string sql = string.Format(@"select c.*,t.type_code,t.type_name
from pts_case as c
left outer join pts_type as t on c.pts_type_id=t.Id
where c.Id=@Id");
List <ConnectSql_mb.cmdParameters> list = new List <ConnectSql_mb.cmdParameters>();
list.Add(new ConnectSql_mb.cmdParameters("@Id", job["Id"], SqlDbType.Int));
DataTable dt = ConnectSql_mb.GetDataTable(sql, list);
string mast = Common.DataRowToJson(dt);
sql = string.Format(@"select *
from pts_case_user
where case_id=@Id");
dt = ConnectSql_mb.GetDataTable(sql, list);
string users = Common.DataTableToJson(dt);
string context = string.Format(@"{0}mast:{2},users:{3}{1}", "{", "}", mast, users);
Common.WriteJsonP(true, context);
}
protected void btn_upload_Click(object sender, EventArgs e)
{
string _name = "";
string error_text = "";
try
{
string _desc = "";
UploadedFile[] files = this.file_upload1.UploadedFiles;
_name = (files[0].FileName ?? "").ToLower().Trim();
if (_name.Length == 0)
{
return;
}
ProcessFile(_name, _desc, files[0].FileBytes, out error_text);
//Auto Payable
//InsertPl();
//Auto Payment
this.lb_txt.Text = error_text.Length > 0 ? error_text : "Upload Error";
}
catch (Exception ex)
{
this.lb_txt.Text = "Upload File fail, pls try agin, error:" + ex.Message;
string user = HttpContext.Current.User.Identity.Name;
string sql1 = string.Format(@"insert into CTM_JobEventLog (CreateDatetime,Controller,JobType,Remark) values (getdate(),@Controller,@JobType,@Remark)");
List <ConnectSql_mb.cmdParameters> list1 = new List <ConnectSql_mb.cmdParameters>();
list1.Add(new ConnectSql_mb.cmdParameters("@Controller", user, SqlDbType.NVarChar, 100));
list1.Add(new ConnectSql_mb.cmdParameters("@JobType", "Import Cargo List", SqlDbType.NVarChar, 100));
list1.Add(new ConnectSql_mb.cmdParameters("@Remark", this.lb_txt.Text, SqlDbType.NVarChar, 300));
ConnectSql_mb.sqlResult re1 = ConnectSql_mb.ExecuteNonQuery(sql1, list1);
}
}
protected bool VilaParty(string code, string uen, string ic, string email1, string email2, string tel1, string tel2, string mobile1, string mobile2, string address, string responsible)
{
bool action = false;
string sql = string.Format(@"select top 1 SequenceId from XXParty order by SequenceId desc");
int sequenceId = SafeValue.SafeInt(ConnectSql.ExecuteScalar(sql), 0);
if (responsible == "个人")
{
sql = string.Format(@"select count(*) from XXParty where Name like N'%{0}%' and CrNo='{1}' and Tel1='{2}'", code, ic, tel1);
}
else
{
sql = string.Format(@"select count(*) from XXParty where Name like N'%{0}%' and CrNo='{1}' and Tel1='{2}'", code, uen, tel1);
}
int n = SafeValue.SafeInt(ConnectSql.ExecuteScalar(sql), 0);
if (n > 0)
{
action = true;
}
else
{
List <ConnectSql_mb.cmdParameters> list = new List <ConnectSql_mb.cmdParameters>();
ConnectSql_mb.cmdParameters cpar = null;
sequenceId = sequenceId + 1;
#region list add
if (code.Length > 4)
{
if (responsible == "个人")
{
cpar = new ConnectSql_mb.cmdParameters("@PartyId", code + "_" + sequenceId, SqlDbType.NVarChar, 100);
list.Add(cpar);
}
else
{
cpar = new ConnectSql_mb.cmdParameters("@PartyId", code.Substring(0, 6) + "_" + sequenceId, SqlDbType.NVarChar, 100);
list.Add(cpar);
}
}
else
{
cpar = new ConnectSql_mb.cmdParameters("@PartyId", code + DateTime.Today.Second, SqlDbType.NVarChar, 100);
list.Add(cpar);
}
cpar = new ConnectSql_mb.cmdParameters("@Code", code, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Name", code, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Status", "USE", SqlDbType.NVarChar);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Address", address, SqlDbType.NVarChar);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Tel1", tel1, SqlDbType.NVarChar);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Tel2", tel2, SqlDbType.NVarChar);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Fax1", mobile1, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Fax2", mobile2, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Email1", email1, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Email2", email2, SqlDbType.NVarChar, 100);
list.Add(cpar);
if (ic.Length > 0)
{
cpar = new ConnectSql_mb.cmdParameters("@CrNo", ic, SqlDbType.NVarChar, 100);
list.Add(cpar);
}
if (uen.Length > 0)
{
cpar = new ConnectSql_mb.cmdParameters("@CrNo", uen, SqlDbType.NVarChar, 100);
list.Add(cpar);
}
sql = string.Format(@"insert into XXParty(PartyId,Code,Name,IsCustomer,Status,Address,Tel1,Tel2,Fax1,Fax2,Email1,Email2,CrNo) values(@PartyId,@Code,@Name,1,@Status,@Address,@Tel1,@Tel2,@Fax1,@Fax2,@Email1,@Email2,@CrNo)");
ConnectSql_mb.sqlResult re = ConnectSql_mb.ExecuteNonQuery(sql, list);
if (re.status)
{
action = true;
}
else
{
}
#endregion
}
return(action);
}
public void ImportExcel(string batch, string file, out string error_text)
{
Aspose.Cells.License license = new Aspose.Cells.License();
license.SetLicense(MapPath("~/Aspose.lic"));
Workbook wb = new Workbook();
if (file.ToLower().EndsWith(".csv"))
{
wb.Open(file, FileFormatType.CSV);
}
else
{
wb.Open(file);
}
int empty_i = 0;
string re_text = "";
Worksheet ws = wb.Worksheets[0];
int existDo = 0;
int successJob = 0;
int successItem = 0;
int errorDo = 0;
//=================================== version 1
bool beginImport = false;
//int existDo = 0;
//int successJob = 0;
int SortIndex = 0;
bool action = false;
//Job Info
string client = ws.Cells["B" + 2].StringValue;
string vessel = ws.Cells["B" + 3].StringValue;
string voyage = ws.Cells["D" + 3].StringValue;
string pol = ws.Cells["B" + 4].StringValue;
string pod = ws.Cells["D" + 4].StringValue;
string warehouse = ws.Cells["H" + 2].StringValue;
string isWarehouse = ws.Cells["J" + 2].StringValue;
DateTime now = DateTime.Now;
string jobNo = "";
C2.CtmJob job = new C2.CtmJob();
jobNo = C2Setup.GetNextNo("", "CTM_Job_WGR", DateTime.Now);
job.JobNo = jobNo;
job.JobType = "WGR";
job.JobDate = DateTime.Now;
job.ClientId = EzshipHelper.GetPartyId(client);
job.DeliveryTo = "";
job.StatusCode = "USE";
job.QuoteNo = jobNo;
job.QuoteStatus = "None";
job.JobStatus = "Confirmed";
job.Vessel = vessel;
job.Voyage = voyage;
job.Pol = pol;
job.Pod = pod;
job.WareHouseCode = warehouse;
job.IsWarehouse = isWarehouse;
C2.Manager.ORManager.StartTracking(job, Wilson.ORMapper.InitialState.Inserted);
C2.Manager.ORManager.PersistChanges(job);
C2Setup.SetNextNo("", "CTM_Job_WGR", jobNo, now);
auto_add_trailer_trip(jobNo, "WGR");
//Order Info
string bkgRefNo = ws.Cells["B" + 7].StringValue;
string hblNo = ws.Cells["F" + 7].StringValue;
string conNo = ws.Cells["I" + 7].StringValue;
string shipper = ws.Cells["B" + 8].StringValue;
string consignee = ws.Cells["B" + 9].StringValue;
string ic_uen = ws.Cells["H" + 9].StringValue;
string responsible = ws.Cells["K" + 9].StringValue;
string email1 = ws.Cells["C" + 10].StringValue;
string email2 = ws.Cells["H" + 10].StringValue;
string tel = ws.Cells["C" + 11].StringValue;
string postCode = ws.Cells["E" + 11].StringValue;
string mobile = ws.Cells["H" + 11].StringValue;
string address = ws.Cells["B" + 12].StringValue;
string receiver = ws.Cells["B" + 14].StringValue;
string contact = ws.Cells["B" + 15].StringValue;
string delivery = ws.Cells["B" + 16].StringValue;
string prepaidInd = ws.Cells["C" + 18].StringValue;
decimal collectAmount1 = SafeValue.SafeDecimal(ws.Cells["H" + 18].StringValue);
decimal collectAmount2 = SafeValue.SafeDecimal(ws.Cells["K" + 18].StringValue);
string dutyPayment = ws.Cells["B" + 19].StringValue;
string incoterm = ws.Cells["B" + 20].StringValue;
decimal ocean_freight = SafeValue.SafeDecimal(ws.Cells["H" + 20].StringValue);
string remark = ws.Cells["B" + 21].StringValue;
string fee1CurrId = ws.Cells["H" + 24].StringValue;
string totalQty = ws.Cells["B" + 24].StringValue;
string packType = ws.Cells["D" + 2].StringValue;
string weight = ws.Cells["F" + 24].StringValue;
string m3 = ws.Cells["J" + 24].StringValue;
List <ConnectSql_mb.cmdParameters> list = new List <ConnectSql_mb.cmdParameters>();
ConnectSql_mb.cmdParameters cpar = null;
#region list add
cpar = new ConnectSql_mb.cmdParameters("@BookingNo", bkgRefNo, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@HblNo", hblNo, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@JobNo", jobNo, SqlDbType.NVarChar);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@RefNo", jobNo, SqlDbType.NVarChar);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@ContNo", conNo, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@ShipperInfo", EzshipHelper.GetPartyId(shipper), SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@ConsigneeInfo", consignee, SqlDbType.NVarChar, 100);
list.Add(cpar);
if (responsible == "个人")
{
cpar = new ConnectSql_mb.cmdParameters("@Responsible", "PERSON", SqlDbType.NVarChar, 100);
list.Add(cpar);
}
else
{
cpar = new ConnectSql_mb.cmdParameters("@Responsible", "COMPANY", SqlDbType.NVarChar, 100);
list.Add(cpar);
}
cpar = new ConnectSql_mb.cmdParameters("@ConsigneeRemark", ic_uen, SqlDbType.NVarChar, 100);
list.Add(cpar);
if (email2.Length > 0)
{
email1 += "," + email2;
}
cpar = new ConnectSql_mb.cmdParameters("@ConsigneeEmail", email1, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@ConsigneeTel", tel, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@ConsigneeZip", postCode, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Mobile1", mobile, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@ConsigneeAddress", address, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@ClientId", receiver, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@ClientEmail", contact, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@ClientAddress", delivery, SqlDbType.NVarChar, 100);
list.Add(cpar);
string prepaid = "NO";
if (prepaidInd.Equals("√"))
{
prepaid = "YES";
}
cpar = new ConnectSql_mb.cmdParameters("@PrepaidInd", prepaid, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@CollectAmount1", collectAmount1, SqlDbType.Decimal, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@CollectAmount2", collectAmount2, SqlDbType.Decimal, 100);
list.Add(cpar);
string payment = "";
if (dutyPayment.Equals("税中国付/DUTY PAID"))
{
payment = "DUTY PAID";
}
else
{
payment = "DUTY UNPAID";
}
cpar = new ConnectSql_mb.cmdParameters("@DutyPayment", payment, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Incoterm", incoterm, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Ocean_Freight", ocean_freight, SqlDbType.Decimal, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Remark1", remark, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Currency", fee1CurrId, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Qty", SafeValue.SafeDecimal(totalQty, 0), SqlDbType.Decimal, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@PackTypeOrig", packType, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Weight", SafeValue.SafeDecimal(weight, 0), SqlDbType.Decimal, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Volume", SafeValue.SafeDecimal(m3, 0), SqlDbType.Decimal, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@JobType", "WGR", SqlDbType.NVarChar, 100);
list.Add(cpar);
#endregion
string sql = string.Format(@"insert into job_house (BookingNo,HblNo,JobNo,RefNo,JobType,ContNo,CargoStatus,ShipperInfo,ConsigneeInfo,Responsible,ConsigneeRemark,
ConsigneeEmail,ConsigneeTel,Mobile1,ConsigneeAddress,ClientId,ClientEmail,ClientAddress,Remark1,Prepaid_Ind,Collect_Amount1,Collect_Amount2,Duty_Payment,Incoterm,Ocean_Freight,Currency,Qty,PackTypeOrig,Weight,Volume,CargoType)
values (@BookingNo,@HblNo,@JobNo,@RefNo,@JobType,@ContNo,'P',@ShipperInfo,@ConsigneeInfo,@Responsible,@ConsigneeRemark,@ConsigneeEmail,@ConsigneeTel,@Mobile1,@ConsigneeAddress,@ClientId,@ClientEmail,@ClientAddress,@Remark1,@PrepaidInd,@CollectAmount1,
@CollectAmount2,@DutyPayment,@Incoterm,@Ocean_Freight,@Currency,@Qty,@PackTypeOrig,@Weight,@Volume,'IN') select @@identity");
ConnectSql_mb.sqlResult re = ConnectSql_mb.ExecuteScalar(sql, list);
if (re.status)
{
string id = SafeValue.SafeString(re.context);
sql = string.Format(@"update job_house set LineId={0} where Id={0}", id);
ConnectSql_mb.ExecuteNonQuery(sql);
//insert_cargo(SafeValue.SafeInt(id, 0));
if (id.Length > 0)
{
action = true;
for (int i = 25; true; i++)
{
if (empty_i >= 10)
{
break;
}
DateTime date = DateTime.Today;
string A = ws.Cells["A" + i].StringValue;
string B = ws.Cells["B" + i].StringValue;
string C = ws.Cells["C" + i].StringValue;
if (A.Trim().ToUpper().Equals("序号"))
{
beginImport = true;
empty_i++;
i = i + 1;
continue;
}
try
{
SortIndex = SafeValue.SafeInt(ws.Cells["A" + i].StringValue, 0);
}
catch
{
empty_i++;
continue;
}
if (C.Length <= 0 && B.Length <= 0)
{
empty_i++;
continue;
}
//empty_i = 0;
if (beginImport)
{
#region
string D = ws.Cells["D" + i].StringValue;
string E = ws.Cells["E" + i].StringValue;
string F = ws.Cells["F" + i].StringValue;
string G = ws.Cells["G" + i].StringValue;
#endregion
//=====================检测是否存在Bill Number
string billNo = D;
string billItem = E;
string sql_check = string.Format(@"select Marks1 from job_stock where OrderId={0} and SortIndex={1} and Marks1='{2}'", id, SortIndex, C);
DataTable dt_check = ConnectSql.GetTab(sql_check);
if (dt_check.Rows.Count > 0)
{
existDo++;
continue;
}
else
{
sql_check = string.Format(@"select count(*) from job_stock where OrderId={0}", id);
int n = SafeValue.SafeInt(ConnectSql.ExecuteScalar(sql_check), 0);
SortIndex = n + 1;
}
try
{
list = new List <ConnectSql_mb.cmdParameters>();
#region list add
cpar = new ConnectSql_mb.cmdParameters("@SortIndex", SortIndex, SqlDbType.Int, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@B", B, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@C", C, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@D", D, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@E", SafeValue.SafeInt(E, 0), SqlDbType.Int);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@F", SafeValue.SafeDecimal(F, 0), SqlDbType.Decimal);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@G", SafeValue.SafeDecimal(G), SqlDbType.Decimal);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@OrderId", id, SqlDbType.Int, 100);
list.Add(cpar);
#endregion
sql = string.Format(@"insert into job_stock (SortIndex,Marks1,Marks2,Uom1,Qty2,Price1,Price2,
OrderId) values (@SortIndex,@B,@C,@D,@E,@F,@G,@OrderId)");
re = ConnectSql_mb.ExecuteNonQuery(sql, list);
if (re.status)
{
successJob++;
//action = true;
}
else
{
errorDo++;
//throw new Exception(re.context);
}
}
catch (Exception ex)
{
errorDo++;
//throw new Exception(ex.ToString());
}
}
else
{
}
}
if (action)
{
//VilaParty(consignee, uen, ic, email1, email2, tel1, tel2, mobile1, mobile2, address, responsible);
}
}
}
re_text = string.Format(@"添加了 {0} 条货物", successJob);
re_text += successItem > 0 ? string.Format(@" {0} 条记录.", successItem) : ".";
re_text += existDo > 0 ? string.Format(@" {0} 条已存在", existDo) : "";
re_text += errorDo > 0 ? string.Format(@" {0} 条错误", errorDo) : "";
error_text = re_text;
}
public void ImportJob(string batch, string file, out string error_text)
{
Aspose.Cells.License license = new Aspose.Cells.License();
license.SetLicense(MapPath("~/Aspose.lic"));
Workbook wb = new Workbook();
if (file.ToLower().EndsWith(".csv"))
{
wb.Open(file, FileFormatType.CSV);
}
else
{
wb.Open(file);
}
int empty_i = 0;
string re_text = "";
Worksheet ws = wb.Worksheets[0];
int existDo = 0;
int successJob = 0;
int successItem = 0;
int errorDo = 0;
//=================================== version 1
bool beginImport = false;
//int existDo = 0;
//int successJob = 0;
int SortIndex = 0;
bool action = false;
string bkgRefNo = ws.Cells["C" + 2].StringValue;
string jobOrder = ws.Cells["F" + 3].StringValue;
string conNo = ws.Cells["J" + 2].StringValue;
string shipper = ws.Cells["C" + 3].StringValue;
string consignee = ws.Cells["C" + 4].StringValue;
string responsible = ws.Cells["L" + 4].StringValue;
string ic = ws.Cells["D" + 5].StringValue;
string uen = ws.Cells["I" + 5].StringValue;
string email1 = ws.Cells["D" + 6].StringValue;
string email2 = ws.Cells["H" + 6].StringValue;
string tel1 = ws.Cells["D" + 7].StringValue;
string tel2 = ws.Cells["F" + 7].StringValue;
string mobile1 = ws.Cells["H" + 7].StringValue;
string mobile2 = ws.Cells["L" + 7].StringValue;
string address = ws.Cells["C" + 8].StringValue;
string receiver = ws.Cells["C" + 10].StringValue;
string contact = ws.Cells["C" + 11].StringValue;
string delivery = ws.Cells["C" + 12].StringValue;
string payable = ws.Cells["C" + 13].StringValue;
string prepaidInd = ws.Cells["D" + 13].StringValue;
decimal collectAmount1 = SafeValue.SafeDecimal(ws.Cells["H" + 13].StringValue);
decimal collectAmount2 = SafeValue.SafeDecimal(ws.Cells["M" + 13].StringValue);
string dutyPayment = ws.Cells["C" + 14].StringValue;
string incoterm = ws.Cells["C" + 15].StringValue;
decimal miscFee = SafeValue.SafeDecimal(ws.Cells["E" + 15].StringValue);
string remark = ws.Cells["C" + 16].StringValue;
string fee1CurrId = ws.Cells["M" + 19].StringValue;
string totalQty = ws.Cells["I" + 19].StringValue;
string packType = ws.Cells["J" + 19].StringValue;
string weight = ws.Cells["K" + 19].StringValue;
string m3 = ws.Cells["L" + 19].StringValue;
List <ConnectSql_mb.cmdParameters> list = new List <ConnectSql_mb.cmdParameters>();
ConnectSql_mb.cmdParameters cpar = null;
#region list add
cpar = new ConnectSql_mb.cmdParameters("@BookingNo", bkgRefNo, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@JobNo", 0, SqlDbType.Int);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@ContNo", conNo, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@ShipperInfo", shipper, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@ConsigneeInfo", consignee, SqlDbType.NVarChar, 100);
list.Add(cpar);
if (responsible == "个人")
{
cpar = new ConnectSql_mb.cmdParameters("@Responsible", "PERSON", SqlDbType.NVarChar, 100);
list.Add(cpar);
}
else
{
cpar = new ConnectSql_mb.cmdParameters("@Responsible", "COMPANY", SqlDbType.NVarChar, 100);
list.Add(cpar);
}
cpar = new ConnectSql_mb.cmdParameters("@ConsigneeRemark", ic, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@ConsigneeEmail", uen, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Email1", email1, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Email2", email2, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Tel1", tel1, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Tel2", tel2, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Mobile1", mobile1, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Mobile2", mobile2, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Desc1", address, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@ClientId", receiver, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@ClientEmail", contact, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Remark1", delivery, SqlDbType.NVarChar, 100);
list.Add(cpar);
string prepaid = "NO";
if (prepaidInd.Equals("√"))
{
prepaid = "YES";
}
cpar = new ConnectSql_mb.cmdParameters("@PrepaidInd", prepaid, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@CollectAmount1", collectAmount1, SqlDbType.Decimal, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@CollectAmount2", collectAmount2, SqlDbType.Decimal, 100);
list.Add(cpar);
string payment = "";
if (dutyPayment.Equals("税中国付/DUTY PAID"))
{
payment = "DUTY PAID";
}
else
{
payment = "DUTY UNPAID";
}
cpar = new ConnectSql_mb.cmdParameters("@DutyPayment", payment, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Incoterm", incoterm, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Ocean_Freight", miscFee, SqlDbType.Decimal, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Remark2", remark, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Currency", fee1CurrId, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Qty", SafeValue.SafeInt(totalQty, 0), SqlDbType.Int, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@PackTypeOrig", packType, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Weight", SafeValue.SafeDecimal(weight, 0), SqlDbType.Decimal, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Volume", SafeValue.SafeDecimal(m3, 0), SqlDbType.Decimal, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@JobType", "E", SqlDbType.NVarChar, 100);
list.Add(cpar);
#endregion
string sql = string.Format(@"insert into job_house (BookingNo,JobNo,JobType,ContNo,CargoStatus,ShipperInfo,ConsigneeInfo,Responsible,ConsigneeRemark,
ConsigneeEmail,Email1,Email2,Tel1,Tel2,Mobile1,Mobile2,Desc1,ClientId,ClientEmail,Remark1,Prepaid_Ind,Collect_Amount1,Collect_Amount2,Duty_Payment,Incoterm,Ocean_Freight,Remark2,Currency,Qty,PackTypeOrig,Weight,Volume)
values (@BookingNo,@JobNo,@JobType,@ContNo,'ORDER',@ShipperInfo,@ConsigneeInfo,@Responsible,@ConsigneeRemark,@ConsigneeEmail,@Email1,@Email2,@Tel1,@Tel2,@Mobile1,@Mobile2,@Desc1,@ClientId,@ClientEmail,@Remark1,@PrepaidInd,@CollectAmount1,@CollectAmount2,@DutyPayment,@Incoterm,@Ocean_Freight,@Remark2,@Currency,@Qty,@PackTypeOrig,@Weight,@Volume)");
ConnectSql_mb.sqlResult re = ConnectSql_mb.ExecuteScalar(sql, list);
string sql_id = string.Format("select top 1 Id from job_house order by Id desc");
string Oid = SafeValue.SafeString(ConnectSql.ExecuteScalar(sql_id));
insert_cargo(SafeValue.SafeInt(Oid, 0));
if (Oid.Length > 0)
{
action = true;
for (int i = 17; true; i++)
{
if (empty_i >= 10)
{
break;
}
DateTime date = DateTime.Today;
string A = ws.Cells["A" + i].StringValue;
string C = ws.Cells["C" + i].StringValue;
if (A.Trim().ToUpper().Equals("序号"))
{
beginImport = true;
empty_i++;
i = i + 1;
continue;
}
try
{
SortIndex = SafeValue.SafeInt(ws.Cells["A" + i].StringValue, 0);
}
catch
{
empty_i++;
continue;
}
if (C.Length <= 0)
{
empty_i++;
continue;
}
//empty_i = 0;
if (beginImport)
{
#region
string D = ws.Cells["D" + i].StringValue;
string E = ws.Cells["E" + i].StringValue;
string F = ws.Cells["F" + i].StringValue;
string G = ws.Cells["G" + i].StringValue;
string H = ws.Cells["H" + i].StringValue;
string I = ws.Cells["I" + i].StringValue;
#endregion
//=====================检测是否存在Bill Number
string billNo = D;
string billItem = E;
string sql_check = string.Format(@"select Marks1 from job_stock where OrderId={0} and SortIndex={1} and Marks1='{2}'", Oid, SortIndex, C);
DataTable dt_check = ConnectSql.GetTab(sql_check);
if (dt_check.Rows.Count > 0)
{
existDo++;
continue;
}
else
{
sql_check = string.Format(@"select count(*) from job_stock where OrderId={0}", Oid);
int n = SafeValue.SafeInt(ConnectSql.ExecuteScalar(sql_check), 0);
SortIndex = n + 1;
}
try
{
list = new List <ConnectSql_mb.cmdParameters>();
#region list add
cpar = new ConnectSql_mb.cmdParameters("@SortIndex", SortIndex, SqlDbType.Int, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@C", C, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@D", D, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@E", SafeValue.SafeInt(F, 0), SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@F", SafeValue.SafeInt(F, 0), SqlDbType.Int);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@G", SafeValue.SafeDecimal(G), SqlDbType.NVarChar);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@H", SafeValue.SafeDecimal(H), SqlDbType.Float);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@OrderId", Oid, SqlDbType.Int, 100);
list.Add(cpar);
#endregion
sql = string.Format(@"insert into job_stock (SortIndex,Marks1,Marks2,Uom1,Qty2,Price1,Price2,
OrderId) values (@SortIndex,@C,@D,@E,@F,@G,@H,@OrderId)");
re = ConnectSql_mb.ExecuteNonQuery(sql, list);
if (re.status)
{
successJob++;
//action = true;
}
else
{
errorDo++;
//throw new Exception(re.context);
}
}
catch (Exception ex)
{
errorDo++;
//throw new Exception(ex.ToString());
}
}
else
{
}
}
if (action)
{
VilaParty(consignee, uen, ic, email1, email2, tel1, tel2, mobile1, mobile2, address, responsible);
}
}
re_text = string.Format(@"添加了 {0} 条货物", successJob);
re_text += successItem > 0 ? string.Format(@" {0} 条记录.", successItem) : ".";
re_text += existDo > 0 ? string.Format(@" {0} 条已存在", existDo) : "";
re_text += errorDo > 0 ? string.Format(@" {0} 条错误", errorDo) : "";
error_text = re_text;
}
protected void btn_search_Click(object sender, EventArgs e)
{
string From = txt_search_dateFrom.Date.ToString("yyyyMMdd");
string To = txt_search_dateTo.Date.AddDays(1).ToString("yyyyMMdd");
string ContNo = txt_search_ContNo.Text;
//bool ContStatus_N = cb_ContStatus1.Checked;
//bool ContStatus_I = cb_ContStatus2.Checked;
//bool ContStatus_CL = cb_ContStatus3.Checked;
//bool ContStatus_R = cb_ContStatus4.Checked;
//bool ContStatus_E = cb_ContStatus5.Checked;
//bool ContStatus_C = cb_ContStatus6.Checked;
//bool ContStatus_UnC = cb_ContStatus7.Checked;
bool cs_New = cb_cs_New.Checked;
bool cs_Completed = cb_cs_Completed.Checked;
bool cs_Uncomplete = false;// cb_cs_Uncomplete.Checked;
bool cs_Import = cb_cs_Import.Checked;
bool cs_Return = cb_cs_Return.Checked;
bool cs_Collection = cb_cs_Collection.Checked;
bool cs_Export = cb_cs_Export.Checked;
bool cs_WHSMT = cb_cs_WHSMT.Checked;
bool cs_WHSLD = cb_cs_WHSLD.Checked;
bool cs_CustomerMT = cb_cs_CustomerMT.Checked;
bool cs_CustomerLD = cb_cs_CustomerLD.Checked;
string JobNo = txt_search_jobNo.Text;
string mastJobNo = txt_search_mastJobNo.Text;
string JobType = search_JobType.Text;
string Vessel = txt_Vessel.Text;
string Client = btn_ClientId.Text;
string NextTrip = SafeValue.SafeString(search_NextTrip.Value);
string subContract = SafeValue.SafeString(cbb_subContract.Value, "ALL");
string oogind = SafeValue.SafeString(cbb_oog.Value, "ALL");
//string ClientName = info["ClientName"].ToString();
List <ConnectSql_mb.cmdParameters> list = new List <ConnectSql_mb.cmdParameters>();
list.Add(new ConnectSql_mb.cmdParameters("@DateFrom", From, SqlDbType.NVarChar, 8));
list.Add(new ConnectSql_mb.cmdParameters("@DateTo", To, SqlDbType.NVarChar, 8));
list.Add(new ConnectSql_mb.cmdParameters("@ContNo", "%" + ContNo, SqlDbType.NVarChar, 100));
list.Add(new ConnectSql_mb.cmdParameters("@JobNo", "%" + JobNo + "%", SqlDbType.NVarChar, 100));
list.Add(new ConnectSql_mb.cmdParameters("@MasterJobNo", "%" + mastJobNo, SqlDbType.NVarChar, 100));
list.Add(new ConnectSql_mb.cmdParameters("@JobType", JobType, SqlDbType.NVarChar, 10));
list.Add(new ConnectSql_mb.cmdParameters("@Vessel", Vessel, SqlDbType.NVarChar, 100));
list.Add(new ConnectSql_mb.cmdParameters("@Client", Client, SqlDbType.NVarChar, 100));
list.Add(new ConnectSql_mb.cmdParameters("@NextTrip", NextTrip, SqlDbType.NVarChar, 10));
list.Add(new ConnectSql_mb.cmdParameters("@subContract", subContract, SqlDbType.NVarChar, 10));
list.Add(new ConnectSql_mb.cmdParameters("@oogInd", oogind, SqlDbType.NVarChar, 10));
string sql = string.Format(@"select job.Id,det1.Id as ContId,det2.Id as TripId,det2.TripIndex,job.JobNo,job.StatusCode as JobStatus,job.JobDate,det1.YardAddress as Depot,job.ClientRefNo,
job.Remark,job.SpecialInstruction,det1.ContainerNo,det1.SealNo,det1.ContainerType,job.EtaDate,job.EtaTime,job.EtdDate,det2.Escort_Ind,det2.Escort_Remark,
det1.UrgentInd,job.OperatorCode,job.CarrierBkgNo,det1.oogInd,det1.CfsStatus,det1.DischargeCell,IsTrucking,IsWarehouse,IsLocal,IsAdhoc,
job.Pol,job.Pod,job.Vessel,job.Voyage,job.PickupFrom,job.DeliveryTo,det1.ScheduleDate,det1.StatusCode ,det1.F5Ind,det1.EmailInd,det1.WarehouseStatus,
det2.DriverCode,det2.TowheadCode,job.MasterJobNo,
(select top 1 Name from XXParty where PartyId=job.ClientId) as client,(select top 1 code from XXParty where PartyId=job.HaulierId) as Haulier,
job.Terminalcode,job.JobType,det1.PortnetStatus,det1.Permit,job.Contractor,job.ClientContact,DATEADD(D,1,det2.BookingDate) as NewScheduleDate,det2.BookingDate,det2.Direct_Inf,
(case when len(DriverCode)>0 then '<span class='''+det2.Statuscode+'''>'+TripCode+'</span>' else '<span class=''X''>'+TripCode+'</span>' end) as trips,
(select (case when len(DriverCode)>0 then '<span class='''+det2.Statuscode+'''>'+TripCode+'</span>' else '<span class=''X''>'+TripCode+'</span>' end) from CTM_JobDet2 as det2 where Det1Id=det1.Id for xml path('')) as trips1,
datediff(hour,isnull((select top 1 convert(nvarchar(8),det2.FromDate,112)+' '+det2.FromTime
from ctm_jobdet2 as det2 where det2.Det1Id=det1.Id and (det2.Statuscode='S' or det2.Statuscode='C') order by det2.FromDate,det2.FromTime),
convert(nvarchar(8),getdate(),112)+' '+convert(nvarchar(5),getdate(),114)),
case when det1.StatusCode='Completed' then isnull((select top 1 convert(nvarchar(8),det2.ToDate,112)+' '+det2.ToTime
from ctm_jobdet2 as det2 where det2.Det1Id=det1.Id and det2.Statuscode='C' order by det2.ToDate desc,det2.ToTime desc),
convert(nvarchar(8),getdate(),112)+' '+convert(nvarchar(5),getdate(),114)) else getdate() end) as hr,
isnull((select ','+det2.Statuscode+':'+TripCode from CTM_JobDet2 as det2 where Det1Id=det1.Id for xml path('')),'')+',' as str_trips,
isnull((select top 1 det2.Statuscode+':'+TripCode from CTM_JobDet2 as det2 where det2.Det1Id=det1.Id order by det2.Id desc),'') as curTrip,det2.ReturnLastDate,det2.FromCode,det2.ToCode,
(case when det2.Det1Id>0 then (select isnull(PermitNo,'')+',' from (
select distinct pm.PermitNo from job_house as h
left outer join ref_permit as pm on h.HblNo=pm.HblNo
where pm.PermitNo<>'' and h.ContId=det2.Det1Id
) as temp for xml path('')) else (select isnull(PermitNo,'')+',' from (
select distinct pm.PermitNo from job_house as h
left outer join ref_permit as pm on h.HblNo=pm.HblNo
where pm.PermitNo<>'' and h.TripId=det2.Id
) as temp for xml path(''))end) as PermitNo
from CTM_JobDet2 as det2 left join CTM_JobDet1 det1 on det2.Det1Id=det1.Id
left join(select p.PermitNo,h.TripId from ref_permit p left join job_house h on p.HblNo=h.HblNo) as tab_p on tab_p.TripId=det2.Id
left outer join CTM_Job as job on job.JobNo=det2.JobNo and job.JobStatus<>'Voided'");
string sql_where = "";
if (ContNo.Length > 0)
{
sql_where = GetWhere(sql_where, "det1.ContainerNo like @ContNo");
}
if (JobNo.Length > 0)
{
sql_where = GetWhere(sql_where, "job.JobNo like @JobNo");
if (JobType.Length > 0 && !JobType.Equals("ALL"))
{
sql_where = GetWhere(sql_where, "det2.TripCode=@JobType");
}
}
if (mastJobNo.Length > 0)
{
sql_where = GetWhere(sql_where, "job.MasterJobNo like @MasterJobNo");
}
string sql_part1 = string.Format(@"select *,(case JobType when 'IMP' then (case when StatusCode='New' then 'IMP' when StatusCode='Import' and curTrip<>'C:IMP' then 'IMP' when StatusCode='WHS-MT' then 'RET' when StatusCode='Return' and curTrip<>'C:RET' then 'RET' else '' end)
when 'EXP' then (case when StatusCode='New' then 'COL' when StatusCode='Collection' and curTrip<>'C:COL'then 'COL' when StatusCode='WHS-LD' then 'EXP' when StatusCode='Export' and curTrip<>'C:EXP' then 'EXP' else '' end)
else '' end) as NextTrip
from(");
string sql_part2 = string.Format(@") as temp");
if (sql_where.Equals(""))
{
//sql_where = " datediff(d,@DateFrom,det2.BookingDate)>=0 and datediff(d,@DateTo,det2.BookingDate)<=0";
sql_where = " @DateFrom<=det2.BookingDate and @DateTo>det2.BookingDate";
if (!cs_Uncomplete)
{
string sql_where_ContStatus = "";
if (cs_New)
{
sql_where_ContStatus += (sql_where_ContStatus.Equals("") ? "" : " or ") + "det1.StatusCode='New'";
}
if (cs_Completed)
{
sql_where_ContStatus += (sql_where_ContStatus.Equals("") ? "" : " or ") + "det1.StatusCode='Completed'";
}
if (cs_Import)
{
sql_where_ContStatus += (sql_where_ContStatus.Equals("") ? "" : " or ") + "det1.StatusCode='Import'";
}
if (cs_Return)
{
sql_where_ContStatus += (sql_where_ContStatus.Equals("") ? "" : " or ") + "det1.StatusCode='Return'";
}
if (cs_Collection)
{
sql_where_ContStatus += (sql_where_ContStatus.Equals("") ? "" : " or ") + "det1.StatusCode='Collection'";
}
if (cs_Export)
{
sql_where_ContStatus += (sql_where_ContStatus.Equals("") ? "" : " or ") + "det1.StatusCode='Export'";
}
//================== warhouse
if (cs_WHSMT)
{
sql_where_ContStatus += (sql_where_ContStatus.Equals("") ? "" : " or ") + "det1.StatusCode='WHS-MT'";
}
if (cs_WHSLD)
{
sql_where_ContStatus += (sql_where_ContStatus.Equals("") ? "" : " or ") + "det1.StatusCode='WHS-LD'";
}
if (cs_CustomerMT)
{
sql_where_ContStatus += (sql_where_ContStatus.Equals("") ? "" : " or ") + "det1.StatusCode='Customer-MT'";
}
if (cs_CustomerLD)
{
sql_where_ContStatus += (sql_where_ContStatus.Equals("") ? "" : " or ") + "det1.StatusCode='Customer-LD'";
}
//===================
if (!sql_where_ContStatus.Equals(""))
{
sql_where = GetWhere(sql_where, "(" + sql_where_ContStatus + ")");
}
}
if (Vessel.Length > 0)
{
sql_where = GetWhere(sql_where, "job.Vessel=@Vessel");
}
if (JobType.Length > 0 && !JobType.Equals("ALL"))
{
sql_where = GetWhere(sql_where, "det2.TripCode=@JobType");
}
if (Client.Length > 0)
{
sql_where = GetWhere(sql_where, "job.clientId=@Client");
}
if (subContract.Length > 0 && subContract != "ALL")
{
sql_where = GetWhere(sql_where, "job.Contractor=@subContract");
}
if (oogind.Length > 0 && oogind != "ALL")
{
if (oogind == "Y")
{
sql_where = GetWhere(sql_where, "det1.oogInd=@oogInd");
}
if (oogind == "N")
{
sql_where = GetWhere(sql_where, "det1.oogInd<>'Y'");
}
}
sql = sql_part1 + sql + " where job.JobType in ('IMP','EXP','LOC') and IsTrucking='Yes' and " + sql_where + sql_part2;
//if (NextTrip.Length > 0 && !NextTrip.Equals("ALL"))
// {
// sql = "select * from (" + sql + ") as temp where NextTrip=@NextTrip order by EtaDate,JobNo,Id,StatusCode desc, JobDate asc";
// }
// else
// {
// if (cs_Uncomplete)
// {
// sql = "select * from (" + sql + ") as temp where StatusCode<>'Completed' and ((JobType='IMP' and CHARINDEX(',C:RET,',str_trips)>0) or (JobType='EXP' and CHARINDEX(',C:EXP,',str_trips)>0)) order by EtaDate,JobNo,Id,StatusCode desc, JobDate asc";
// }
// else
// {
// sql += " order by EtaDate,JobNo,Id,StatusCode desc, JobDate asc";
// }
// }
}
else
{
sql = sql_part1 + sql + " where job.JobType in ('IMP','EXP','LOC') and IsTrucking='Yes' and " + sql_where + sql_part2;
sql += " order by EtaDate,JobNo,Id,StatusCode desc,ScheduleDate asc";
}
DataTable dt = ConnectSql_mb.GetDataTable(sql, list);
//throw new Exception(sql.ToString());
this.grid_Transport.DataSource = dt;
this.grid_Transport.DataBind();
}
private string update_cont(int id, string jobNo, string contNo, string sealNo, string contType, DateTime schedule, string contIndex
, string clearStatus, DateTime clearDate, string verify, DateTime verifyDate, string remark, DateTime valiDate,
DateTime scheduleStartDate, DateTime billingDate, DateTime dispatchDate, DateTime etaDate)
{
string result = "";
#region list
Wilson.ORMapper.OPathQuery query = new Wilson.ORMapper.OPathQuery(typeof(C2.JobHouse), "JobNo='" + jobNo + "' and ContNo='" + contIndex + "'");
ObjectSet objSet = C2.Manager.ORManager.GetObjectSet(query);
if (objSet.Count > 0)
{
#region
for (int j = 0; j < objSet.Count; j++)
{
C2.JobHouse house = objSet[j] as C2.JobHouse;
house.ContNo = contNo;
house.OpsType = "Delivery";
house.CargoStatus = "C";
C2.Manager.ORManager.StartTracking(house, Wilson.ORMapper.InitialState.Updated);
C2.Manager.ORManager.PersistChanges(house);
}
#endregion
}
string sql = "";
List <ConnectSql_mb.cmdParameters> cmd = new List <ConnectSql_mb.cmdParameters>();
sql = string.Format(@"update ctm_jobdet1 set ContainerNo=@ContainerNo,SealNo=@SealNo,
ContainerType=@ContainerType,ScheduleDate=@ScheduleDate,StatusCode=@StatusCode,CustomsClearDate=@CustomsClearDate,
CustomsClearStatus=@CustomsClearStatus,CustomsVerifyInd=@CustomsVerifyInd,CustomsVerifyDate=@CustomsVerifyDate,CustomsRemark=@CustomsRemark,ValiDate=@ValiDate,ScheduleStartDate=@ScheduleStartDate,BillingDate=@BillingDate,DispatchDate=@DispatchDate
where JobNo=@JobNo and ContainerNo=@ContNo");
cmd = new List <ConnectSql_mb.cmdParameters>();
if (valiDate <= new DateTime(1900, 01, 01))
{
valiDate = etaDate.AddDays(-1);
}
if (clearDate <= new DateTime(1900, 01, 01))
{
clearDate = etaDate.AddDays(1);
}
if (verifyDate <= new DateTime(1900, 01, 01))
{
verifyDate = etaDate.AddDays(1);
}
if (schedule <= new DateTime(1900, 01, 01))
{
schedule = etaDate.AddDays(1);
}
if (billingDate <= new DateTime(1900, 01, 01))
{
billingDate = etaDate.AddDays(1);
}
if (scheduleStartDate < new DateTime(1900, 01, 01))
{
scheduleStartDate = etaDate.AddDays(1);
}
if (dispatchDate <= new DateTime(1900, 01, 01))
{
dispatchDate = etaDate.AddDays(3);
}
cmd.Add(new ConnectSql_mb.cmdParameters("@ContainerNo", contNo, SqlDbType.NVarChar));
cmd.Add(new ConnectSql_mb.cmdParameters("@SealNo", sealNo, SqlDbType.NVarChar));
cmd.Add(new ConnectSql_mb.cmdParameters("@ContainerType", contType, SqlDbType.NVarChar));
cmd.Add(new ConnectSql_mb.cmdParameters("@JobNo", jobNo, SqlDbType.NVarChar));
cmd.Add(new ConnectSql_mb.cmdParameters("@ContNo", contIndex, SqlDbType.NVarChar));
cmd.Add(new ConnectSql_mb.cmdParameters("@StatusCode", "Export", SqlDbType.NVarChar));
cmd.Add(new ConnectSql_mb.cmdParameters("@ScheduleDate", schedule, SqlDbType.DateTime));
cmd.Add(new ConnectSql_mb.cmdParameters("@CustomsClearDate", clearDate, SqlDbType.DateTime));
cmd.Add(new ConnectSql_mb.cmdParameters("@CustomsClearStatus", clearStatus, SqlDbType.NVarChar));
cmd.Add(new ConnectSql_mb.cmdParameters("@CustomsVerifyInd", verify, SqlDbType.NVarChar));
cmd.Add(new ConnectSql_mb.cmdParameters("@CustomsVerifyDate", verifyDate, SqlDbType.DateTime));
cmd.Add(new ConnectSql_mb.cmdParameters("@CustomsRemark", remark, SqlDbType.NVarChar));
cmd.Add(new ConnectSql_mb.cmdParameters("@ValiDate", valiDate, SqlDbType.DateTime));
cmd.Add(new ConnectSql_mb.cmdParameters("@ScheduleStartDate", scheduleStartDate, SqlDbType.DateTime));
cmd.Add(new ConnectSql_mb.cmdParameters("@BillingDate", billingDate, SqlDbType.DateTime));
cmd.Add(new ConnectSql_mb.cmdParameters("@DispatchDate", dispatchDate, SqlDbType.DateTime));
ConnectSql_mb.sqlResult res = ConnectSql_mb.ExecuteNonQuery(sql, cmd);
if (res.status)
{
result = "Save Success!";
}
else
{
result = "Save Error!";
}
#endregion
return(result);
}
public void ImportJob(string batch, string file, out string error_text)
{
//throw new Exception("importjob");
Aspose.Cells.License license = new Aspose.Cells.License();
license.SetLicense(MapPath("~/Aspose.lic"));
//Workbook wb = new Workbook();
byte[] byData = new byte[100];
char[] charData = new char[1000];
List <string> list = new List <string>();
if (file.ToLower().EndsWith(".txt"))
{
using (System.IO.StreamReader sr = new System.IO.StreamReader(file, true))
{
string str;
while ((str = sr.ReadLine()) != null)
{
list.Add(str);
}
}
}
int empty_i = 0;
string re_text = "";
//Worksheet ws = wb.Worksheets[0];
int existDo = 0;
int successJob = 0;
int successItem = 0;
int errorDo = 0;
string notInside = "";
int insideDo = 0;
string sql = "";
//throw new Exception("here");
//=================================== version 1
bool beginImport = false;
int _row = 0;
DateTime payDate = DateTime.Today;
string value = "";
string cur = "SGD";
int docId = 0;
int index = 0;
for (int i = 0; i < list.Count; i++)
{
_row++;
if (empty_i >= 10)
{
break;
}
DateTime date = DateTime.Today;
string line = list[i];
string[] ar = line.Split('~');
if (i == 0)
{
ar = line.Split(' ');
payDate = SafeValue.SafeDate(ar[7], date);
docId = InsertPay(payDate, cur);
}
if (i == list.Count - 1)
{
ar = line.Split('~');
decimal locAmt = SafeValue.SafeDecimal(ar[4]);
sql = string.Format(@"update XAApPayment set DocAmt={1}, LocAmt={1} where SequenceId={0}", docId, locAmt);
ConnectSql_mb.ExecuteNonQuery(sql);
continue;
}
if (i == 4)
{
ar = line.Split(':');
cur = ar[1];
}
if (ar[0] == "Invoice No.")
{
beginImport = true;
empty_i++;
continue;
}
//throw new Exception(ws.Cells["A" + i].Type.ToString());
try
{
value = list[i];
}
catch
{
empty_i++;
continue;
}
if (line.Length <= 0)
{
empty_i++;
continue;
}
empty_i = 0;
if (beginImport)
{
index++;
string invNo = SafeValue.SafeString(ar[0]);
DateTime invDate = SafeValue.SafeDate(ar[1], date);
//string docType = SafeValue.SafeString(ar[2]);
decimal amt = SafeValue.SafeDecimal(ar[4]);
//=====================检测是否存在Bill Number
string billDate = invDate.ToString("yyyy-MM-dd");
string sql_check = string.Format(@"select count(*) from XAApPaymentDet where Remark1='{0}'", invNo);
int check_n = SafeValue.SafeInt(ConnectSql.ExecuteScalar(sql_check), 0);
if (check_n > 0)
{
existDo++;
continue;
}
else
{
try
{
sql_check = string.Format(@"select SequenceId,DocNo,DocType,CurrencyId,DocDate from XAApPayable where SupplierBillNo='{0}' and SupplierBillDate='{1}'", invNo, billDate);
DataTable dt_check = ConnectSql.GetTab(sql_check);
if (dt_check.Rows.Count > 0)
{
string docNo = SafeValue.SafeString(dt_check.Rows[0]["DocNo"]);
string docType = SafeValue.SafeString(dt_check.Rows[0]["DocType"]);
DateTime docDate = SafeValue.SafeDate(dt_check.Rows[0]["DocDate"], DateTime.Today);
InsertPay_Det(docId, docNo, docType, index, invNo, "", "", 1, amt, cur, 1, 0, "", "", "", "", invDate);
successJob++;
}
else
{
insideDo++;
if (insideDo == 1)
{
notInside += invNo;
}
else
{
notInside += invNo + ",";
}
}
}
catch (Exception ex)
{
//errorDo++;
throw new Exception(_row.ToString() + "##" + ex.Message);
}
}
}
else
{
}
}
sql = string.Format(@"select DocNo,DocDate,LocAmt from XAApPayment where SequenceId={0}", docId);
DataTable dt_pay = ConnectSql_mb.GetDataTable(sql);
string billNo = "";
decimal totalAmt = 0;
DateTime docDate1 = DateTime.Today;
if (dt_pay.Rows.Count > 0)
{
billNo = SafeValue_mb.SafeString(dt_pay.Rows[0]["DocNo"]);
totalAmt = SafeValue.SafeDecimal(dt_pay.Rows[0]["LocAmt"]);
docDate1 = SafeValue.SafeDate(dt_pay.Rows[0]["DocDate"], DateTime.Today);
}
re_text = string.Format(@"uploaded {0} lines", successJob);
re_text += successItem > 0 ? string.Format(@" {0} items.", successItem) : ".";
re_text += existDo > 0 ? string.Format(@" {0} existed", existDo) : "";
re_text += errorDo > 0 ? string.Format(@" {0} error", errorDo) : "";
re_text += insideDo > 0 ? string.Format(@" ,{0} not inside the payable", "Invoice:【" + notInside + "】") : "";
error_text = re_text;
string user = HttpContext.Current.User.Identity.Name;
string sql1 = string.Format(@"insert into CTM_JobEventLog (CreateDatetime,Controller,JobType,Remark,Note1,Note2,Value1) values (getdate(),@Controller,@JobType,@Remark,@Note1,@Note2,@Value1)");
List <ConnectSql_mb.cmdParameters> list1 = new List <ConnectSql_mb.cmdParameters>();
list1.Add(new ConnectSql_mb.cmdParameters("@Controller", user, SqlDbType.NVarChar, 100));
list1.Add(new ConnectSql_mb.cmdParameters("@JobType", "GIRO", SqlDbType.NVarChar, 100));
list1.Add(new ConnectSql_mb.cmdParameters("@Remark", re_text, SqlDbType.NVarChar, 300));
list1.Add(new ConnectSql_mb.cmdParameters("@Note1", billNo, SqlDbType.NVarChar, 300));
list1.Add(new ConnectSql_mb.cmdParameters("@Note2", docDate1.ToString("yyyy-MM-dd"), SqlDbType.NVarChar, 300));
list1.Add(new ConnectSql_mb.cmdParameters("@Value1", totalAmt, SqlDbType.NVarChar, 300));
ConnectSql_mb.sqlResult re1 = ConnectSql_mb.ExecuteNonQuery(sql1, list1);
}
protected void grid1_CustomDataCallback(object sender, ASPxGridViewCustomDataCallbackEventArgs e)
{
string par = e.Parameters;
string no = SafeValue.SafeString(Request.QueryString["no"]);
string jobNo = SafeValue.SafeString(Request.QueryString["jobNo"]);
string status = SafeValue.SafeString(Request.QueryString["status"]);
if (Request.QueryString["no"] != null)
{
try
{
if (par == "Cont")
{
#region Container
if (status == "Quoted")
{
no = jobNo;
}
string sql = string.Format(@"select ContainerNo,ContainerType,(case when isnull(BillingRefNo,'')<>''then job.JobNo else '' end) as SubJobNo from CTM_JobDet1 det1 inner join CTM_Job job on det1.JobNo=job.JobNo where job.JobNo='{0}' or job.BillingRefNo='{0}'", no);
DataTable dt = ConnectSql_mb.GetDataTable(sql);
for (int a = 0; a < dt.Rows.Count; a++)
{
string contNo = SafeValue.SafeString(dt.Rows[a]["ContainerNo"]);
string contType = SafeValue.SafeString(dt.Rows[a]["ContainerType"]);
string subJobNo = SafeValue.SafeString(dt.Rows[a]["SubJobNo"]);
for (int i = 0; i < list.Count; i++)
{
int n = SafeValue.SafeInt(ConnectSql_mb.ExecuteScalar(string.Format(@"select count(*) from job_cost where (JobNo='{0}' or JobNo='{1}') and LineType!='DP'", jobNo, no)), 0);
n = n + 1;
int id = list[i].id;
Wilson.ORMapper.OPathQuery query = new Wilson.ORMapper.OPathQuery(typeof(C2.JobRate), "Id=" + id + "");
C2.JobRate rate = C2.Manager.ORManager.GetObject(query) as C2.JobRate;
string scope = SafeValue.SafeString(rate.BillScope, "");
string contSize = SafeValue.SafeString(rate.ContSize, "");
string str = "";
if (contType.Length > 2)
{
str = contType.Substring(0, 2);
}
decimal price = 0;
if (status == "Rate")
{
if (rate.BillScope == "CONT")
{
insert_job_cost(rate, no, subJobNo, contNo, "", contType, contSize, str, par.ToUpper(), n);
}
}
else
{
insert_job_cost(rate, jobNo, subJobNo, contNo, "", contType, contSize, str, par.ToUpper(), n);
}
if (subJobNo.Length > 0)
{
insert_job_cost(rate, subJobNo, "", contNo, "", contType, contSize, str, par.ToUpper(), n);
}
}
}
e.Result = "Success";
#endregion
}
if (par == "Job")
{
#region Job
for (int i = 0; i < list.Count; i++)
{
int n = SafeValue.SafeInt(ConnectSql_mb.ExecuteScalar(string.Format(@"select count(*) from job_cost where JobNo='{0}' and LineType!='DP'", jobNo)), 0);
n = n + 1;
int id = list[i].id;
Wilson.ORMapper.OPathQuery query = new Wilson.ORMapper.OPathQuery(typeof(C2.JobRate), "Id=" + id + "");
C2.JobRate rate = C2.Manager.ORManager.GetObject(query) as C2.JobRate;
if (status == "Rate")
{
//if (rate.BillScope == "JOB")
//{
insert_job_cost(rate, no, "", "", "", "", "", "", par.ToUpper(), n);
//}
}
else
{
insert_job_cost(rate, jobNo, "", "", "", "", "", "", par.ToUpper(), n);
}
}
e.Result = "Success";
#endregion
}
if (par == "Trip")
{
#region Trip
if (status == "Quoted")
{
no = jobNo;
}
string sql = string.Format(@"select det2.Id,ChessisCode,job.JobNo,BillingRefNo,(case when isnull(BillingRefNo,'')<>''then job.JobNo else '' end) as SubJobNo from CTM_JobDet2 det2 inner join CTM_Job job on det2.JobNo=job.JobNo where job.JobNo='{0}' or job.BillingRefNo='{0}' and (Self_Ind='No' or isnull(Self_Ind,'')='')", no);
DataTable dt = ConnectSql_mb.GetDataTable(sql);
for (int a = 0; a < dt.Rows.Count; a++)
{
string tripId = SafeValue.SafeString(dt.Rows[a]["Id"]);
string contNo = SafeValue.SafeString(dt.Rows[a]["ChessisCode"]);
string subJobNo = SafeValue.SafeString(dt.Rows[a]["SubJobNo"]);
for (int i = 0; i < list.Count; i++)
{
int n = SafeValue.SafeInt(ConnectSql_mb.ExecuteScalar(string.Format(@"select count(*) from job_cost where (JobNo='{0}' or JobNo='{1}') and LineType!='DP'", jobNo, no)), 0);
n = n + 1;
int id = list[i].id;
Wilson.ORMapper.OPathQuery query = new Wilson.ORMapper.OPathQuery(typeof(C2.JobRate), "Id=" + id + "");
C2.JobRate rate = C2.Manager.ORManager.GetObject(query) as C2.JobRate;
if (status == "Rate")
{
if (rate.BillScope.ToUpper() == "TRIP")
{
insert_job_cost(rate, no, subJobNo, contNo, tripId, "", "", "", par.ToUpper(), n);
}
}
else
{
insert_job_cost(rate, jobNo, subJobNo, contNo, tripId, "", "", "", par.ToUpper(), n);
}
if (subJobNo.Length > 0)
{
insert_job_cost(rate, subJobNo, "", contNo, tripId, "", "", "", par.ToUpper(), n);
}
}
}
e.Result = "Success";
#endregion
}
}
catch { }
}
}
public static DataSet DsQuotation(string orderNo, string docType)
{
DataSet set = new DataSet();
#region Quotation
DataTable mast = InitMastDataTable();
DataRow row = mast.NewRow();
Wilson.ORMapper.OPathQuery job = new Wilson.ORMapper.OPathQuery(typeof(C2.CtmJob), "QuoteNo='" + orderNo + "'");
C2.CtmJob ctmJob = C2.Manager.ORManager.GetObject(job) as C2.CtmJob;
if (ctmJob != null)
{
row["Vessel"] = ctmJob.Vessel;
row["Voyage"] = ctmJob.Voyage;
row["Eta"] = ctmJob.EtaDate.ToString("dd/MM/yy");
row["CustRefNo"] = ctmJob.ClientRefNo;
row["Pol"] = ctmJob.Pol;
row["Pod"] = ctmJob.Pod;
row["CrBkgNo"] = ctmJob.CarrierBkgNo;
row["HblNo"] = ctmJob.CarrierBlNo;
row["QuoteNo"] = ctmJob.QuoteNo;
row["QuoteDate"] = ctmJob.QuoteDate.ToString("dd/MM/yy");
row["JobNo"] = ctmJob.JobNo;
row["BillType"] = "QUOTATION";
row["UserId"] = HttpContext.Current.User.Identity.Name;
row["PartyName"] = EzshipHelper.GetPartyName(ctmJob.ClientId);
string sql = string.Format(@"select Address from XXParty where PartyId='{0}'", ctmJob.ClientId);
row["PartyAdd"] = C2.Manager.ORManager.ExecuteScalar(sql);
row["Terms"] = ctmJob.IncoTerm;
row["CompanyName"] = System.Configuration.ConfigurationManager.AppSettings["CompanyName"];
row["MoneyWords"] = "";
row["QuoteRemark"] = ctmJob.QuoteRemark;
row["JobDes"] = ctmJob.JobDes;
row["TerminalRemark"] = ctmJob.TerminalRemark;
decimal subTotal = 0;
decimal gstTotal = 0;
DataTable details = InitDetailDataTable();
sql = string.Format(@"select count(*) from job_rate where JobNo='{0}' and ChgCode='LUMPSUM'", orderNo);
int count = SafeValue.SafeInt(ConnectSql_mb.ExecuteScalar(sql), 0);
string sql_n = "";
if (count > 0)
{
sql = string.Format(@"select LineType,LineStatus,JobNo,JobType,ClientId,BillScope,BillType,BillClass,ContSize,ContType,ChgCode,ChgCodeDes,Remark,Price from job_rate where JobNo='{0}' and ChgCode='LUMPSUM'", orderNo);
sql_n = string.Format(@"select LineType,LineStatus,JobNo,JobType,ClientId,BillScope,BillType,BillClass,ContSize,ContType,ChgCode,ChgCodeDes,Remark,Price from job_rate where JobNo='{0}' and ChgCode<>'LUMPSUM'", orderNo);
}
else
{
sql = string.Format(@"select LineType,LineStatus,JobNo,JobType,ClientId,BillScope,BillType,BillClass,ContSize,ContType,ChgCode,ChgCodeDes,Remark,Price from job_rate where JobNo='{0}'", orderNo);
}
DataTable det = ConnectSql.GetTab(sql);
for (int i = 0; i < det.Rows.Count; i++)
{
DataRow row1 = details.NewRow();
string chgCode = SafeValue.SafeString(det.Rows[i]["ChgCode"]);
string chgCodeDes = SafeValue.SafeString(det.Rows[i]["ChgCodeDes"]);
row1["No"] = i + 1;
string chgDes = "";
DataTable det_n = ConnectSql.GetTab(sql_n);
for (int j = 0; j < det_n.Rows.Count; j++)
{
chgDes += "- " + SafeValue.SafeString(det_n.Rows[j]["ChgCodeDes"]) + "\n";
}
if (chgCode == "LUMPSUM")
{
chgCodeDes = ctmJob.LumSumRemark;
}
row1["ChgDes1"] = chgCodeDes;
row1["OtherChgDes"] = chgDes;
row1["ChgCode"] = SafeValue.SafeString(det.Rows[i]["ChgCode"]);
row1["Qty"] = 1;
row1["Rmk"] = SafeValue.SafeString(det.Rows[i]["Remark"]);
row1["LineAmt"] = SafeValue.ChinaRound(1 * SafeValue.SafeDecimal(det.Rows[i]["Price"]), 2);
string sql_chgCode = string.Format(@"select top 1 ChgcodeId,ChgcodeDes,GstP,GstTypeId,ChgTypeId from XXChgCode where ChgcodeDes='{0}'", chgCode);
DataTable dt_chgCode = ConnectSql.GetTab(sql_chgCode);
decimal gst = 0;
string gstType = "";
string chgTypeId = "";
if (dt_chgCode.Rows.Count > 0)
{
gst = SafeValue.SafeDecimal(dt_chgCode.Rows[0]["GstP"]);
gstType = SafeValue.SafeString(dt_chgCode.Rows[0]["GstTypeId"]);
chgTypeId = SafeValue.SafeString(dt_chgCode.Rows[0]["ChgTypeId"]);
}
row1["Price"] = string.Format("{0:#,##0.00}", SafeValue.SafeString(det.Rows[i]["Price"]));
row1["Gst"] = SafeValue.SafeInt(gst * 100, 0) + "% " + gstType + "R";
decimal amt = SafeValue.ChinaRound(1 * SafeValue.SafeDecimal(det.Rows[i]["Price"], 0), 2);
decimal gstAmt = SafeValue.ChinaRound((amt * gst), 2);
row1["Amt"] = amt;
row1["GstAmt"] = gstAmt;
row1["LocAmt"] = amt + gstAmt;
subTotal += amt;
gstTotal += gstAmt;
details.Rows.Add(row1);
}
row["SubTotal"] = subTotal;
row["GstTotal"] = gstTotal;
row["TotalAmt"] = subTotal + gstTotal;
mast.Rows.Add(row);
set.Tables.Add(mast);
set.Tables.Add(details);
}
#endregion
return(set);
}
protected bool UpdateParty(string name, string uen, string ic, string email1, string email2, string tel1, string tel2, string mobile1, string mobile2, string address, string responsible)
{
string sql = string.Format(@"select count(*) from XXParty where Name like N'%{0}%' and (CrNo='{1}' or CrNo='{2}')", name, uen, ic);
int count = SafeValue.SafeInt(ConnectSql.ExecuteScalar(sql), 0);
bool action = false;
if (count > 0)
{
#region Update Party
List <ConnectSql_mb.cmdParameters> list = new List <ConnectSql_mb.cmdParameters>();
ConnectSql_mb.cmdParameters cpar = null;
string code = "";
if (name.Length > 4)
{
if (responsible != "个人")
{
code = name.Substring(0, 4) + DateTime.Today.Second;
}
else
{
code = name;
}
}
else
{
code = name;
}
if (ic.Length > 0)
{
#region
cpar = new ConnectSql_mb.cmdParameters("@Code", code, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Name", name, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Address", address, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Tel1", tel1, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Tel2", tel2, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Fax1", "", SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Fax2", "", SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Email1", email1, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Email2", email2, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@CrNo", ic, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Value", "like '%" + name + "%'", SqlDbType.NVarChar, 100);
list.Add(cpar);
sql = string.Format(@"update XXParty set Code=@Code,Name=@Name,Address=@Address,Tel1=@Tel1,Tel2=@Tel2,Fax1=@Fax1,Fax2=@Fax2,Email1=@Email1,Email2=@Email2,CrNo=@CrNo where Name=@Value or CrNo=@CrNo");
ConnectSql_mb.sqlResult re = ConnectSql_mb.ExecuteNonQuery(sql, list);
if (re.status)
{
action = true;
}
#endregion
}
if (uen.Length > 0)
{
#region
cpar = new ConnectSql_mb.cmdParameters("@Code", code, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Name", name, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Address", address, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Tel1", tel1, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Tel2", tel2, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Fax1", "", SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Fax2", "", SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Email1", email1, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Email2", email2, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@CrNo", uen, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Value", "like '%" + name + "%'", SqlDbType.NVarChar, 100);
list.Add(cpar);
sql = string.Format(@"update XXParty set Code=@Code,Name=@Name,Address=@Address,Tel1=@Tel1,Tel2=@Tel2,Fax1=@Fax1,Fax2=@Fax2,Email1=@Email1,Email2=@Email2,CrNo=@CrNo where Name=@Value or CrNo=@CrNo");
ConnectSql_mb.sqlResult re = ConnectSql_mb.ExecuteNonQuery(sql, list);
if (re.status)
{
action = true;
}
#endregion
}
#endregion
}
return(action);
}
private void Job_New_Save(DevExpress.Web.ASPxGridView.ASPxGridViewCustomDataCallbackEventArgs e)
{
DateTime date = DateTime.Now;
string time4 = date.ToString("HHmm");
string jobType = SafeValue.SafeString(cbb_new_jobtype.Value, "IMP");
string jobStatus = SafeValue.SafeString(cbb_new_jobstatus.Text, "Quoted");
//string jobType1 = "IMP";
//if (jobType.IndexOf("EXP") > -1)
//{
// jobType1 = "EXP";
//}
string jobType1 = jobType;
string isTrucking = SafeValue.SafeString(cmb_IsTrucking.Value);
string isWarehouse = SafeValue.SafeString(cmb_IsWarehouse.Value);
string subContract = SafeValue.SafeString(cbb_Contractor.Value);
string warehouseCode = SafeValue.SafeString(txt_new_WareHouseId.Text);
string jobno = "";
string user = HttpContext.Current.User.Identity.Name;
string quoteNo = "";
string quoteStatus = "Pending";
if (jobStatus == "Quoted")
{
quoteNo = C2Setup.GetNextNo("", "CTM_Job_" + jobStatus, date);
}
else
{
quoteStatus = "None";
jobno = C2Setup.GetNextNo("", "CTM_Job_" + jobType1, date);
quoteNo = jobno;
}
string sql = string.Format(@"insert into CTM_Job (JobNo,JobDate,EtaDate,EtdDate,CodDate,StatusCode,CreateBy,CreateDatetime,UpdateBy,UpdateDatetime,EtaTime,EtdTime,JobType,ClientId,YardRef,PickupFrom,DeliveryTo,Remark,WarehouseAddress,JobStatus,QuoteNo,QuoteStatus,QuoteDate,WareHouseCode,IsTrucking,IsWarehouse,Contractor) values ('{0}','{4}',getdate(),getdate(),getdate(),'USE','{1}',getdate(),'{1}',getdate(),'{2}','{2}','{3}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{14}',getdate(),'{13}','{15}','{16}','{17}') select @@identity", jobno, user, time4, cbb_new_jobtype.Value, txt_new_JobDate.Date, btn_new_ClientId.Text, txt_DepotAddress.Text, txt_FromAddress.Text, txt_ToAddress.Text, txt_new_remark.Text, txt_WarehouseAddress.Text, jobStatus, quoteNo, warehouseCode, quoteStatus, isTrucking, isWarehouse, subContract);
int jobId = SafeValue.SafeInt(ConnectSql_mb.ExecuteScalar(sql), 0);
if (jobId > 0)
{
string userId = HttpContext.Current.User.Identity.Name;
C2.CtmJobEventLog elog = new C2.CtmJobEventLog();
elog.Platform_isWeb();
elog.Controller = userId;
if (jobStatus == "Quoted")
{
elog.setActionLevel(jobId, CtmJobEventLogRemark.Level.Quotation, 1);
elog.ActionLevel_isQuoted(jobId);
C2Setup.SetNextNo("", "CTM_Job_" + jobStatus, quoteNo, date);
//GetJobRate(quoteNo, btn_new_ClientId.Text, SafeValue.SafeString(cbb_new_jobtype.Value));
e.Result = quoteNo;
}
else
{
elog.setActionLevel(jobId, CtmJobEventLogRemark.Level.Job, 1);
elog.ActionLevel_isJOB(jobId);
C2Setup.SetNextNo("", "CTM_Job_" + jobType1, jobno, date);
e.Result = jobno;
}
elog.log();
sql = string.Format(@"update CTM_Job set JobNo={0} where Id={0}", jobId);
ConnectSql.ExecuteSql(sql);
}
}
public static DataSet DsImpTs(string orderNo, string docType)
{
DataSet set = new DataSet();
if (docType == "IV" || docType == "IV1")
{
ObjectQuery query = new ObjectQuery(typeof(C2.XAArInvoice), "DocNo='" + orderNo + "'", "");
ObjectSet objSet = C2.Manager.ORManager.GetObjectSet(query);
if (objSet.Count == 0 || objSet[0] == null)
{
return(new DataSet());
}
C2.XAArInvoice obj = objSet[0] as C2.XAArInvoice;
#region Invoice
DataTable mast = InitMastDataTable();
DataRow row = mast.NewRow();
string mastRefNo = obj.MastRefNo;
string mastType = obj.MastType;
Wilson.ORMapper.OPathQuery job = new Wilson.ORMapper.OPathQuery(typeof(C2.CtmJob), "JobNo='" + mastRefNo + "'");
C2.CtmJob ctmJob = C2.Manager.ORManager.GetObject(job) as C2.CtmJob;
if (ctmJob != null)
{
row["Vessel"] = ctmJob.Vessel;
row["Voyage"] = ctmJob.Voyage;
row["Eta"] = ctmJob.EtaDate.ToString("dd.MM.yy");
row["CustRefNo"] = ctmJob.ClientRefNo;
row["Pol"] = ctmJob.Pol;
row["Pod"] = ctmJob.Pod;
row["CrBkgNo"] = ctmJob.CarrierBkgNo;
row["HblNo"] = ctmJob.CarrierBlNo;
}
row["JobNo"] = mastRefNo;
row["BillType"] = "TAX INVOICE";
row["DocNo"] = obj.DocNo;
row["DocDate"] = obj.DocDate.ToString("dd/MM/yyyy");
row["DocDueDate"] = obj.DocDueDate.ToString("dd/MM/yyyy");
row["UserId"] = HttpContext.Current.User.Identity.Name;
row["PartyName"] = obj.PartyName;
row["PartyTo"] = obj.PartyTo;
string sql = string.Format(@"select Address,Contact1,Fax1,Tel1,CrNo from XXParty where PartyId='{0}'", obj.PartyTo);
DataTable tab = ConnectSql_mb.GetDataTable(sql);
if (tab.Rows.Count > 0)
{
row["PartyAdd"] = tab.Rows[0]["Address"].ToString();
row["Contact1"] = tab.Rows[0]["Contact1"].ToString();
row["Fax1"] = tab.Rows[0]["Fax1"].ToString();
row["Tel1"] = tab.Rows[0]["Tel1"].ToString();
row["CrNo"] = tab.Rows[0]["CrNo"].ToString();
}
row["Terms"] = obj.Term;
row["Terms"] = obj.Term;
row["CompanyName"] = System.Configuration.ConfigurationManager.AppSettings["CompanyName"];
row["MoneyWords"] = "";
row["Remark"] = SafeValue.SafeString(ConnectSql.ExecuteScalar(string.Format(@"select Description from XAArInvoice where MastRefNo='{0}'", mastRefNo)));
decimal subTotal = 0;
decimal gstTotal = obj.TaxAmt;
DataTable details = InitDetailDataTable();
sql = string.Format(@"select ChgCode,Qty,MastType,DocNo,ChgDes1,Price,Gst,GstAmt,Unit,
LineLocAmt, Currency,ExRate,
LocAmt,JobRefNo,GstType from XAArInvoiceDet where DocId={0} order by Gst desc", obj.SequenceId);
DataTable det = ConnectSql.GetTab(sql);
for (int i = 0; i < det.Rows.Count; i++)
{
DataRow row1 = details.NewRow();
string cntType = SafeValue.SafeString(det.Rows[i]["JobRefNo"]);
string chgCode = SafeValue.SafeString(det.Rows[i]["ChgCode"]);
row1["No"] = i + 1;
row1["DocNo"] = SafeValue.SafeString(det.Rows[i]["DocNo"]);
row1["ChgDes1"] = SafeValue.SafeString(det.Rows[i]["ChgDes1"]);
row1["ChgCode"] = SafeValue.SafeString(det.Rows[i]["ChgCode"]);
row1["LineAmt"] = SafeValue.SafeString(det.Rows[i]["LineLocAmt"]);
row1["Unit"] = SafeValue.SafeString(det.Rows[i]["Unit"]);
row1["Currency"] = SafeValue.SafeString(det.Rows[i]["Currency"]);
row1["ExRate"] = SafeValue.SafeString(det.Rows[i]["ExRate"]);
row1["Price"] = string.Format("{0:#,##0.00}", SafeValue.SafeString(det.Rows[i]["Price"]));
row1["Gst"] = SafeValue.SafeInt(SafeValue.SafeDecimal(det.Rows[i]["Gst"]) * 100, 0) + "% " + det.Rows[i]["GstType"] + "R";
decimal amt = SafeValue.ChinaRound(SafeValue.SafeInt(det.Rows[i]["Qty"], 0) * SafeValue.SafeDecimal(det.Rows[i]["Price"], 0), 2);
decimal gstAmt = SafeValue.ChinaRound((amt * SafeValue.SafeDecimal(det.Rows[i]["Gst"], 0)), 2);
row1["Amt"] = amt;
row1["GstAmt"] = SafeValue.SafeString(det.Rows[i]["GstAmt"]);
row1["LocAmt"] = SafeValue.SafeString(det.Rows[i]["LocAmt"]);
row1["Qty"] = SafeValue.SafeInt(det.Rows[i]["Qty"], 0);
details.Rows.Add(row1);
subTotal += amt;
}
sql = string.Format(@"select ContainerNo,SealNo,ContainerType from CTM_JobDet1 where JobNo='{0}' ", mastRefNo);
DataTable cnt = ConnectSql.GetTab(sql);
for (int i = 0; i < cnt.Rows.Count; i++)
{
if (cnt.Rows.Count - i > 1)
{
row["ContainerNo"] += SafeValue.SafeString(cnt.Rows[i]["ContainerNo"]) + " / " + SafeValue.SafeString(cnt.Rows[i]["SealNo"])
+ " / " + SafeValue.SafeString(cnt.Rows[i]["ContainerType"]) + "\n";
}
else
{
row["ContainerNo"] += SafeValue.SafeString(cnt.Rows[i]["ContainerNo"]) + " / " + SafeValue.SafeString(cnt.Rows[i]["SealNo"])
+ " / " + SafeValue.SafeString(cnt.Rows[i]["ContainerType"]);
}
}
row["SubTotal"] = subTotal;
row["GstTotal"] = gstTotal;
row["TotalAmt"] = subTotal + gstTotal;
mast.Rows.Add(row);
#endregion
set.Tables.Add(mast);
set.Tables.Add(details);
}
if (docType == "PL" || docType == "PL1")
{
ObjectQuery query = new ObjectQuery(typeof(C2.XAApPayable), "DocNo='" + orderNo + "'", "");
ObjectSet objSet = C2.Manager.ORManager.GetObjectSet(query);
if (objSet.Count == 0 || objSet[0] == null)
{
return(new DataSet());
}
C2.XAApPayable obj = objSet[0] as C2.XAApPayable;
#region PL
DataTable mast = InitMastDataTable();
DataRow row = mast.NewRow();
string mastRefNo = obj.MastRefNo;
string mastType = obj.MastType;
if (mastType == "CTM")
{
Wilson.ORMapper.OPathQuery job = new Wilson.ORMapper.OPathQuery(typeof(C2.CtmJob), "JobNo='" + mastRefNo + "'");
C2.CtmJob ctmJob = C2.Manager.ORManager.GetObject(job) as C2.CtmJob;
row["Vessel"] = ctmJob.Vessel;
row["Voyage"] = ctmJob.Voyage;
row["Eta"] = ctmJob.EtaDate.ToString("dd.MM.yy");
}
row["DocNo"] = obj.DocNo;
row["DocDate"] = obj.DocDate.ToString("dd/MM/yyyy");
row["SupplierBillNo"] = obj.SupplierBillNo;
row["SupplierBillDate"] = obj.SupplierBillDate.ToString("dd/MM/yyyy");
row["PartyName"] = obj.PartyName;
string sql = string.Format(@"select Address from XXParty where PartyId='{0}'", obj.PartyTo);
row["PartyAdd"] = C2.Manager.ORManager.ExecuteScalar(sql);
row["Terms"] = obj.Term;
decimal subTotal = 0;
decimal gstTotal = 0;
DataTable details = InitDetailDataTable();
sql = string.Format(@"select ChgCode,sum(Qty) as Qty,MastType,max(DocNo) DocNo,max(ChgDes1) as ChgDes1,Price,max(Gst) Gst,max(GstAmt) GstAmt,
max(LocAmt) LocAmt,max(JobRefNo) JobRefNo,max(GstType) GstType from XAApPayableDet where DocId={0} group by ChgCode,MastType,Price order by Gst desc", obj.SequenceId);
DataTable det = ConnectSql.GetTab(sql);
for (int i = 0; i < det.Rows.Count; i++)
{
DataRow row1 = details.NewRow();
string cntType = SafeValue.SafeString(det.Rows[i]["JobRefNo"]);
string chgCode = SafeValue.SafeString(det.Rows[i]["ChgCode"]);
row1["DocNo"] = SafeValue.SafeString(det.Rows[i]["DocNo"]);
row1["ChgDes1"] = SafeValue.SafeString(det.Rows[i]["ChgDes1"]);
row1["ChgCode"] = SafeValue.SafeString(det.Rows[i]["ChgCode"]);
row1["Price"] = string.Format("{0:#,##0.00}", SafeValue.SafeString(det.Rows[i]["Price"]));
row1["Gst"] = SafeValue.SafeInt(SafeValue.SafeDecimal(det.Rows[i]["Gst"]) * 100, 0) + "% " + det.Rows[i]["GstType"] + "R";
decimal amt = SafeValue.ChinaRound(SafeValue.SafeInt(det.Rows[i]["Qty"], 0) * SafeValue.SafeDecimal(det.Rows[i]["Price"], 0), 2);
decimal gstAmt = SafeValue.ChinaRound((amt * SafeValue.SafeDecimal(det.Rows[i]["Gst"], 0)), 2);
row1["Amt"] = amt;
row1["GstAmt"] = SafeValue.SafeString(det.Rows[i]["GstAmt"]);
row1["LocAmt"] = SafeValue.SafeString(det.Rows[i]["LocAmt"]);
row1["Qty"] = SafeValue.SafeInt(det.Rows[i]["Qty"], 0);
details.Rows.Add(row1);
subTotal += amt;
gstTotal += gstAmt;
}
row["SubTotal"] = subTotal;
row["GstTotal"] = gstTotal;
row["Total"] = subTotal + gstTotal;
mast.Rows.Add(row);
#endregion
set.Tables.Add(mast);
set.Tables.Add(details);
}
return(set);
}
public void ImportJob(string batch, string file, out string error_text)
{
Aspose.Cells.License license = new Aspose.Cells.License();
license.SetLicense(MapPath("~/Aspose.lic"));
Workbook wb = new Workbook();
if (file.ToLower().EndsWith(".csv"))
{
wb.Open(file, FileFormatType.CSV);
}
else
{
wb.Open(file);
}
int empty_i = 0;
string re_text = "";
Worksheet ws = wb.Worksheets[0];
int existDo = 0;
int successJob = 0;
int successItem = 0;
int errorDo = 0;
//=================================== version 1
bool beginImport = false;
//int existDo = 0;
//int successJob = 0;
for (int i = 1; true; i++)
{
if (empty_i >= 10)
{
break;
}
string A = ws.Cells["A" + i].StringValue;
string B = ws.Cells["B" + i].StringValue;
if (B.Length <= 0)
{
empty_i++;
continue;
}
empty_i = 0;
if (beginImport)
{
string C = ws.Cells["C" + i].StringValue;
string D = ws.Cells["D" + i].StringValue;
string E = ws.Cells["E" + i].StringValue;
string F = ws.Cells["F" + i].StringValue;
string G = ws.Cells["G" + i].StringValue;
string H = ws.Cells["H" + i].StringValue;
string I = ws.Cells["I" + i].StringValue;
I = I.Replace(" ", "");
string J = ws.Cells["J" + i].StringValue;
string K = ws.Cells["K" + i].StringValue;
string L = ws.Cells["L" + i].StringValue;
string M = ws.Cells["M" + i].StringValue;
string N = ws.Cells["N" + i].StringValue;
string O = ws.Cells["O" + i].StringValue;
string P = ws.Cells["P" + i].StringValue;
string Q = ws.Cells["Q" + i].StringValue;
string R = ws.Cells["R" + i].StringValue;
string S = ws.Cells["S" + i].StringValue;
string T = ws.Cells["T" + i].StringValue;
string U = ws.Cells["U" + i].StringValue;
string V = ws.Cells["V" + i].StringValue;
string W = ws.Cells["W" + i].StringValue;
string X = ws.Cells["X" + i].StringValue;
string Y = ws.Cells["Y" + i].StringValue;
string Z = ws.Cells["Z" + i].StringValue;
string AA = ws.Cells["AA" + i].StringValue;
string AB = ws.Cells["AB" + i].StringValue;
string AC = ws.Cells["AC" + i].StringValue;
string AD = ws.Cells["AD" + i].StringValue;
string AE = ws.Cells["AE" + i].StringValue;
string AF = ws.Cells["AF" + i].StringValue;
string AG = ws.Cells["AG" + i].StringValue;
string AH = ws.Cells["AH" + i].StringValue;
string AI = ws.Cells["AI" + i].StringValue;
string AJ = ws.Cells["AJ" + i].StringValue;
string AK = ws.Cells["AK" + i].StringValue;
string AL = ws.Cells["AL" + i].StringValue;
string AM = ws.Cells["AM" + i].StringValue;
string AN = ws.Cells["AN" + i].StringValue;
string AO = ws.Cells["AO" + i].StringValue;
string AP = ws.Cells["AP" + i].StringValue;
string AQ = ws.Cells["AQ" + i].StringValue;
string AR = ws.Cells["AR" + i].StringValue;
string AS = ws.Cells["AS" + i].StringValue;
string AT = ws.Cells["AT" + i].StringValue;
string AU = ws.Cells["AU" + i].StringValue;
string AV = ws.Cells["AV" + i].StringValue;
string AW = ws.Cells["AW" + i].StringValue;
string AX = ws.Cells["AX" + i].StringValue;
string AY = ws.Cells["AY" + i].StringValue;
string AZ = ws.Cells["AZ" + i].StringValue;
string BA = ws.Cells["BA" + i].StringValue;
string BB = ws.Cells["BB" + i].StringValue;
string BC = ws.Cells["BC" + i].StringValue;
string BD = ws.Cells["BD" + i].StringValue;
string BE = ws.Cells["BE" + i].StringValue;
string BF = ws.Cells["BF" + i].StringValue;
string BG = ws.Cells["BG" + i].StringValue;
string BH = ws.Cells["BH" + i].StringValue;
string BI = ws.Cells["BI" + i].StringValue;
string BJ = ws.Cells["BJ" + i].StringValue;
string BK = ws.Cells["BK" + i].StringValue;
string BL = ws.Cells["BL" + i].StringValue;
string BM = ws.Cells["BM" + i].StringValue;
string BN = ws.Cells["BN" + i].StringValue;
string BO = ws.Cells["BO" + i].StringValue;
string BP = ws.Cells["BP" + i].StringValue;
string BQ = ws.Cells["BQ" + i].StringValue;
string BR = ws.Cells["BR" + i].StringValue;
string BS = ws.Cells["BS" + i].StringValue;
string BT = ws.Cells["BT" + i].StringValue;
string BU = ws.Cells["BU" + i].StringValue;
string BV = ws.Cells["BV" + i].StringValue;
string BW = ws.Cells["BW" + i].StringValue;
string BX = ws.Cells["BX" + i].StringValue;
string BY = ws.Cells["BY" + i].StringValue;
//=====================检测是否存在Bill Number
string billNo = D;
string billItem = E;
string sql_check = string.Format(@"select [F1] from psa_bill where [BILL NUMBER]='{0}' and [BILL ITEM NUMBER]='{1}'", billNo, billItem);
DataTable dt_check = ConnectSql.GetTab(sql_check);
if (dt_check.Rows.Count > 0)
{
existDo++;
continue;
}
try
{
List <ConnectSql_mb.cmdParameters> list = new List <ConnectSql_mb.cmdParameters>();
string A_t = SafeValue.SafeDate(A, new DateTime(1900, 1, 1)).ToString("yyyyMMdd");
if (A.Length > 10)
{
string[] ar_t = A.Split(' ');
if (ar_t.Length == 2)
{
DateTime dt_temp = SafeValue.SafeDate(ar_t[0], new DateTime(1900, 1, 1));
for (int A_i = 1; i < ar_t.Length; A_i++)
{
if (ar_t[A_i].Length > 0)
{
A_t = dt_temp.ToString("yyyyMMdd") + " " + ar_t[A_i];
break;
}
}
}
}
//ConnectSql_mb.cmdParameters cpar = new ConnectSql_mb.cmdParameters("@A", SafeValue.SafeDate(A, new DateTime(1900, 1, 1)), SqlDbType.DateTime);
//ConnectSql_mb.cmdParameters cpar = new ConnectSql_mb.cmdParameters("@A", A_t, SqlDbType.NVarChar, 100);
ConnectSql_mb.cmdParameters cpar = new ConnectSql_mb.cmdParameters("@A", reGetDateFormat(A, file), SqlDbType.NVarChar, 100);
list.Add(cpar);
#region list add
cpar = new ConnectSql_mb.cmdParameters("@B", B, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@C", C, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@D", D, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@E", SafeValue.SafeDecimal(E, 0), SqlDbType.Float);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@F", SafeValue.SafeDecimal(F, 0), SqlDbType.Float);
list.Add(cpar);
//cpar = new ConnectSql_mb.cmdParameters("@G", SafeValue.SafeDate(G, new DateTime(1900, 1, 1)), SqlDbType.DateTime);
cpar = new ConnectSql_mb.cmdParameters("@G", reGetDateFormat(G, file), SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@H", H, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@I", I, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@J", SafeValue.SafeDecimal(J, 0), SqlDbType.Float);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@K", K, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@L", SafeValue.SafeDecimal(L, 0), SqlDbType.Float);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@M", M, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@N", SafeValue.SafeDecimal(N, 0), SqlDbType.Float);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@O", SafeValue.SafeDecimal(O, 0), SqlDbType.Float);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@P", P, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Q", Q, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@R", R, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@S", S, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@T", T, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@U", U, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@V", V, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@W", W, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@X", SafeValue.SafeDecimal(X, 0), SqlDbType.Float);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Y", Y, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@Z", Z, SqlDbType.NVarChar, 100);
list.Add(cpar);
//cpar = new ConnectSql_mb.cmdParameters("@AA", SafeValue.SafeDate(AA, new DateTime(1900, 1, 1)), SqlDbType.DateTime);
string _AA = reGetDateFormat(AA, file);
cpar = new ConnectSql_mb.cmdParameters("@AA", _AA, SqlDbType.NVarChar, 100);
list.Add(cpar);
//cpar = new ConnectSql_mb.cmdParameters("@AB", SafeValue.SafeDate(AB, new DateTime(1900, 1, 1)), SqlDbType.DateTime);
string _AB = reGetDateFormat(AB, file);
DateTime _ETA = new DateTime(S.Int(BP.Substring(10, 4)), S.Int(BP.Substring(7, 2)).S.Int(BP.Substring(4, 2)));
string ETA1 = _ETA.AddDays(-3).ToString("yyyy-MM-dd");
string ETA2 = _ETA.AddDays(3).ToString("yyyy-MM-dd");
string JOBTYPE = "IMP";
if (AT == "O")
{
JOBTYPE = "EXP";
}
string JOB = D.Text("select top 1 c.jobno from ctm_job j, ctm_jobdet1 c where j.jobno=c.jobno and j.etadate>'" + ETA1 + "' and j.etadate<'" + ETA2 + "' and c.containerno='" + I + "' AND (j.JobType = '" + JOBTYPE + "') ");
cpar = new ConnectSql_mb.cmdParameters("@AB", reGetDateFormat(_AB, file), SqlDbType.NVarChar, 100);
list.Add(cpar);
//cpar = new ConnectSql_mb.cmdParameters("@AC", SafeValue.SafeDate(AC, new DateTime(1900, 1, 1)), SqlDbType.DateTime);
cpar = new ConnectSql_mb.cmdParameters("@AC", reGetDateFormat(AC, file), SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@AD", AD, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@AE", AE, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@AF", AF, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@AG", AG, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@AH", AH, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@AI", AI, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@AJ", AJ, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@AK", AK, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@AL", AL, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@AM", AM, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@AN", reGetDateFormat(AN, file), SqlDbType.DateTime);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@AO", reGetDateFormat(AO, file), SqlDbType.DateTime);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@AP", AP, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@AQ", AQ, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@AR", AR, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@AS", AS, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@AT", AT, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@AU", AU, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@AV", AV, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@AW", AW, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@AX", SafeValue.SafeDecimal(AX, 0), SqlDbType.Float);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@AY", AY, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@AZ", AZ, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@BA", BA, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@BB", BB, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@BC", BC, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@BD", BD, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@BE", BE, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@BF", BF, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@BG", SafeValue.SafeDecimal(BG, 0), SqlDbType.Float);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@BH", BH, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@BI", SafeValue.SafeDecimal(BI, 0), SqlDbType.Float);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@BJ", BJ, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@BK", BK, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@BL", BL, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@BM", BM, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@BN", BN, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@BO", BO, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@BP", BP, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@BQ", BQ, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@BR", BR, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@BS", SafeValue.SafeDecimal(BS, 0), SqlDbType.Float);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@BT", BT, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@BU", BU, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@BV", BV, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@BW", BW, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@BX", BX, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@BY", BY, SqlDbType.NVarChar, 100);
list.Add(cpar);
cpar = new ConnectSql_mb.cmdParameters("@JOB", JOB, SqlDbType.NVarChar, 100);
list.Add(cpar);
#endregion
string sql = string.Format(@"insert into psa_bill (JOB_NO,F1,[BILL TYPE],[BILLING COMPANY],[BILL NUMBER],[BILL ITEM NUMBER],[ACCOUNT NUMBER],[BILL DATE],[REF NUMBER],[CONTAINER NUMBER],
[TARIFF CODE],[TARIFF DESCRIPTION],RATE,[UNIT DESCRIPTION],[BILLABLE UNIT],AMOUNT,[FULL VESSEL NAME],[FULL OUT VOY NUMBER],[FULL IN VOY NUMBER],
[ABBR VESSEL NAME],[ABBR OUT VOY NUMBER],[ABBR IN VOY NUMBER],[LINE CODE],[GROSS TONNAGE],LOA,[SERVICE ROUTE],[IN SERVICE ROUTE],[LAST BTR DATE],
[ATB DATE],[ATU DATE],[FIRST ACTIVITY DATE],[LAST ACTIVITY DATE],[CONNECTING FULL VSL NAME],[CONNECTING FULL OUT VOY NUMBER],[CONNECTING ABBR VSL NAME],
[CONNECTING ABBR VOY NUMBER],[CONNECTING SERVICE ROUTE],[CONNECTING IN SERVICE ROUTE],[CONNECTING VESSEL COD DATE],[CONNECTING VESSEL ATB DATE],
[SERVICE START DATE],[SERVICE END DATE],[LOCATION FROM],[LOCATION TO],[BERTH NUMBER],[SLOT OPERATOR],[LOAD/DISC INDICATOR],[FROM],[TO],[CNTR TYPE],[CNTR SIZE],
[ISO SIZE TYPE],[DG IMO CLASS],[TRANSHIP INDICATOR],[DEPOT INDICATOR],[REASON CODE],[LADEN STATUS],[CNTR OPERATOR],[GST INDICATOR],[GST PERCENTAGE],
[CURRENCY CODE],[EXCHANGE RATE],[ORG CODE],[CHARGE CATEGORY],[CHARGE TYPE],[CHARGE CLASSIFICATION 1],[CHARGE CLASSIFICATION 2],[CHARGE DESCRIPTION],
[DESCRIPTION LINE 1],[DESCRIPTION LINE 2],[DISCOUNT TARIFF CODE],[DISCOUNT PERCENT],[CUSTOMER REF 1],[CUSTOMER REF 2],[CUSTOMER REF 3],
[CUSTOMER REF 4],[CUSTOMER REF 5],[CUSTOMER REF 6]) values (@JOB,@A,@B,@C,@D,@E,@F,@G,@H,@I,@J,@K,@L,@M,@N,@O,@P,@Q,@R,@S,@T,@U,@V,@W,@X,@Y,@Z,
@AA,@AB,@AC,@AD,@AE,@AF,@AG,@AH,@AI,@AJ,@AK,@AL,@AM,@AN,@AO,@AP,@AQ,@AR,@AS,@AT,@AU,@AV,@AW,@AX,@AY,@AZ,
@BA,@BB,@BC,@BD,@BE,@BF,@BG,@BH,@BI,@BJ,@BK,@BL,@BM,@BN,@BO,@BP,@BQ,@BR,@BS,@BT,@BU,@BV,@BW,@BX,@BY)");
//ConnectSql_mb.sqlResult re = ConnectSql_mb.ExecuteNonQuery(sql, list);
SqlConnection sqlConn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["local"].ConnectionString);
SqlCommand cmd = new SqlCommand(sql, sqlConn);
sqlConn.Open();
foreach (cmdParameters par in list)
{
if (par.size == 0)
{
com.Parameters.Add(par.name, par.type);
}
else
{
com.Parameters.Add(par.name, par.type, par.size);
}
com.Parameters[par.name].Value = par.value;
}
object o = cmd.ExecuteNonQuery();
sqlConn.Close();
//if (re.status)
//{
successJob++;
//}
//else
//{
// errorDo++;
//throw new Exception(re.context);
//}
}
catch (Exception ex)
{
errorDo++;
//throw new Exception(ex.ToString());
}
}
else
{
if (B.Trim().ToUpper().Equals("BILL TYPE"))
{
beginImport = true;
}
}
}
re_text = string.Format(@"uploaded {0} jobs", successJob);
re_text += successItem > 0 ? string.Format(@" {0} items.", successItem) : ".";
re_text += existDo > 0 ? string.Format(@" {0} existed", existDo) : "";
re_text += errorDo > 0 ? string.Format(@" {0} error", errorDo) : "";
error_text = re_text;
string user = HttpContext.Current.User.Identity.Name;
string sql1 = string.Format(@"insert into CTM_JobEventLog (CreateDatetime,Controller,JobType,Remark) values (getdate(),@Controller,@JobType,@Remark)");
List <ConnectSql_mb.cmdParameters> list1 = new List <ConnectSql_mb.cmdParameters>();
list1.Add(new ConnectSql_mb.cmdParameters("@Controller", user, SqlDbType.NVarChar, 100));
list1.Add(new ConnectSql_mb.cmdParameters("@JobType", "PSA", SqlDbType.NVarChar, 100));
list1.Add(new ConnectSql_mb.cmdParameters("@Remark", re_text, SqlDbType.NVarChar, 300));
ConnectSql_mb.sqlResult re1 = ConnectSql_mb.ExecuteNonQuery(sql1, list1);
}
protected void grid_Transport_CustomDataCallback(object sender, ASPxGridViewCustomDataCallbackEventArgs e)
{
string par = e.Parameters;
if (par == "Locked")
{
if (list.Count > 0)
{
for (int i = 0; i < list.Count; i++)
{
int id = list[i].id;
string sql = string.Format(@"update ctm_jobdet2 set TripStatus='LOCKED' where Id={0}", id);
ConnectSql_mb.ExecuteScalar(sql);
}
e.Result = "Success";
}
}
else if (par == "UnLocked")
{
if (list.Count > 0)
{
for (int i = 0; i < list.Count; i++)
{
int id = list[i].id;
string sql = string.Format(@"update ctm_jobdet2 set TripStatus='' where Id={0}", id);
ConnectSql_mb.ExecuteScalar(sql);
}
e.Result = "Success";
}
}
else if (par == "Paid")
{
if (list.Count > 0)
{
for (int i = 0; i < list.Count; i++)
{
int id = list[i].id;
string sql = string.Format(@"update ctm_jobdet2 set TripStatus='PAID' where Id={0}", id);
ConnectSql_mb.ExecuteScalar(sql);
}
e.Result = "Success";
}
}
else if (par == "UnPaid")
{
if (list.Count > 0)
{
for (int i = 0; i < list.Count; i++)
{
int id = list[i].id;
string sql = string.Format(@"update ctm_jobdet2 set TripStatus='' where Id={0}", id);
ConnectSql_mb.ExecuteScalar(sql);
}
e.Result = "Success";
}
}
}
protected void ASPxGridView1_CustomDataCallback(object sender, ASPxGridViewCustomDataCallbackEventArgs e)
{
ASPxTextBox Id = ASPxGridView1.FindEditFormTemplateControl("txt_Oid") as ASPxTextBox;
string s = e.Parameters;
string[] ar = s.Split('_');
if (ar.Length >= 2)
{
if (ar[0].Equals("Printline"))
{
#region
int rowIndex = SafeValue.SafeInt(ar[1], -1);
ASPxLabel lbl_Id = this.ASPxGridView1.FindRowCellTemplateControl(rowIndex, null, "lbl_Id") as ASPxLabel;
string sql = string.Format(@"select FromDate,ToDate,Person from Hr_Payroll where Id={0}", SafeValue.SafeInt(lbl_Id.Text, 0));
DataTable dt = ConnectSql.GetTab(sql);
string Person = "";
DateTime FromDate = DateTime.Now;
DateTime ToDate = DateTime.Now;
if (dt.Rows.Count > 0)
{
Person = SafeValue.SafeString(dt.Rows[0]["Person"]);
FromDate = SafeValue.SafeDate(dt.Rows[0]["FromDate"], DateTime.Now);
ToDate = SafeValue.SafeDate(dt.Rows[0]["ToDate"], DateTime.Now);
}
e.Result = FromDate + "," + ToDate + "," + lbl_Id.Text + "," + Person;
#endregion
}
if (ar[0].Equals("Editline"))
{
#region
int rowIndex = SafeValue.SafeInt(ar[1], -1);
ASPxLabel lbl_Id = this.ASPxGridView1.FindRowCellTemplateControl(rowIndex, null, "lbl_Id") as ASPxLabel;
e.Result = lbl_Id.Text;
#endregion
}
if (ar[0].Equals("Deleteline"))
{
#region
int rowIndex = SafeValue.SafeInt(ar[1], -1);
ASPxLabel lbl_Id = this.ASPxGridView1.FindRowCellTemplateControl(rowIndex, null, "lbl_Id") as ASPxLabel;
string sql = string.Format(@"delete from Hr_Payroll where Id='{0}'", SafeValue.SafeInt(lbl_Id.Text, 0));
int res = ConnectSql_mb.ExecuteNonQuery(sql);
if (res > 0)
{
e.Result = "Success";
}
#endregion
}
}
else
{
#region
if (s == "Confirm")
{
string update_sql = string.Format(@"update Hr_Payroll set StatusCode='Confirm' where StatusCode='Draft'");
ConnectSql.ExecuteSql(update_sql);
e.Result = "Success!";
}
if (s == "UnConfirm")
{
string update_sql = string.Format(@"update Hr_Payroll set StatusCode='Draft' where Id={0}", SafeValue.SafeInt(Id.Text, 0));
ConnectSql.ExecuteSql(update_sql);
e.Result = "Success!";
}
if (s == "UnCancel")
{
string update_sql = string.Format(@"update Hr_Payroll set StatusCode='Draft' where Id={0}", SafeValue.SafeInt(Id.Text, 0));
ConnectSql.ExecuteSql(update_sql);
e.Result = "Success!";
}
if (s == "Payroll")
{
string value = "Action Error! Pls check again";
string sql = string.Format(@"select Person,sum(Amt) as TotalAmt,max(Remark) as Remark from Hr_Quote where (person in (select Id from hr_person where Status='EMPLOYEE')) group by Person");
DataTable tab = ConnectSql.GetTab(sql);
int fromYear = txt_from.Date.Year;
int fromMonth = txt_from.Date.Month;
string name = HttpContext.Current.User.Identity.Name;
int toYear = txt_end.Date.Year;
int toMonth = txt_end.Date.Month;
bool result = false;
string from = txt_from.Date.ToString("yyyy-MM-dd");
string to = txt_end.Date.ToString("yyyy-MM-dd");
int month = toMonth - fromMonth;
DateTime firstDayOfFromMonth = new DateTime(fromYear, fromMonth, 1);
DateTime lastDayOfFromMonth = new DateTime(fromYear, fromMonth, DateTime.DaysInMonth(fromYear, fromMonth));
DateTime firstDayOfToMonth = new DateTime(toYear, toMonth, 1);
DateTime lastDayOfToMonth = new DateTime(toYear, toMonth, DateTime.DaysInMonth(toYear, toMonth));
if (month < 2)
{
for (int a = 0; a <= month; a++)
{
for (int i = 0; i < tab.Rows.Count; i++)
{
int person = SafeValue.SafeInt(tab.Rows[i]["Person"], 0);
string sql_pay = string.Format(@"select count(*) from Hr_Payroll where Person={0} and (FromDate>='{1}' and ToDate<='{2}')", person, firstDayOfFromMonth.ToString("yyyy-MM-dd"), lastDayOfFromMonth.ToString("yyyy-MM-dd"));
int cnt = SafeValue.SafeInt(ConnectSql.ExecuteScalar(sql_pay), 0);
if (cnt == 0)
{
#region From Date
decimal amt = SafeValue.SafeDecimal(tab.Rows[i]["TotalAmt"]);
string remark = SafeValue.SafeString(tab.Rows[i]["Remark"]);
HrPayroll payroll = new HrPayroll();
payroll.Person = person;
payroll.FromDate = firstDayOfFromMonth;
payroll.ToDate = lastDayOfFromMonth;
payroll.StatusCode = "Draft";
payroll.Term = "";
payroll.Remark = "";
payroll.Pic = "";
payroll.CreateBy = name;
payroll.CreateDateTime = DateTime.Now;
payroll.Amt = amt;
payroll.AutoInd = "Yes";
Manager.ORManager.StartTracking(payroll, Wilson.ORMapper.InitialState.Inserted);
Manager.ORManager.PersistChanges(payroll);
sql = string.Format(@"select * from Hr_Quote where Person={0} ", person);
DataTable tabDet = ConnectSql.GetTab(sql);
for (int j = 0; j < tabDet.Rows.Count; j++)
{
string code = SafeValue.SafeString(tabDet.Rows[j]["PayItem"]);
string des = SafeValue.SafeString(tabDet.Rows[j]["Remark"]);
decimal payamt = SafeValue.SafeDecimal(tabDet.Rows[j]["Amt"]);
decimal before = SafeValue.SafeDecimal(tabDet.Rows[j]["Amt"]);
sql = string.Format(@"insert into Hr_PayrollDet(PayrollId,ChgCode,Description,Amt,CreateBy,CreateDateTime,Before) values({0},'{1}','{2}',{3},'{4}',getdate(),{3})", payroll.Id, code, des.Replace("'", ""), payamt, name);
ConnectSql.ExecuteSql(sql);
}
auto_calculate(person, payroll.Id);
//InsertLeave(payroll.Id, person, from, to, name);
C2.HrPayroll.UpdateMaster(payroll.Id);
#endregion
result = true;
}
string sql_pay_to = string.Format(@"select count(*) from Hr_Payroll where Person={0} and (FromDate>='{1}' and ToDate<='{2}')", person, firstDayOfToMonth.ToString("yyyy-MM-dd"), lastDayOfToMonth.ToString("yyyy-MM-dd"));
int cnt_to = SafeValue.SafeInt(ConnectSql.ExecuteScalar(sql_pay_to), 0);
if (cnt_to == 0)
{
#region To Date
decimal amt = SafeValue.SafeDecimal(tab.Rows[i]["TotalAmt"]);
string remark = SafeValue.SafeString(tab.Rows[i]["Remark"]);
HrPayroll payroll = new HrPayroll();
payroll.Person = person;
payroll.FromDate = firstDayOfToMonth;
payroll.ToDate = lastDayOfToMonth;
payroll.StatusCode = "Draft";
payroll.Term = "";
payroll.Remark = "";
payroll.Pic = "";
payroll.CreateBy = name;
payroll.CreateDateTime = DateTime.Now;
payroll.Amt = amt;
payroll.AutoInd = "Yes";
Manager.ORManager.StartTracking(payroll, Wilson.ORMapper.InitialState.Inserted);
Manager.ORManager.PersistChanges(payroll);
sql = string.Format(@"select * from Hr_Quote where Person={0} ", person);
DataTable tabDet = ConnectSql.GetTab(sql);
for (int j = 0; j < tabDet.Rows.Count; j++)
{
string code = SafeValue.SafeString(tabDet.Rows[j]["PayItem"]);
string des = SafeValue.SafeString(tabDet.Rows[j]["Remark"]);
decimal payamt = SafeValue.SafeDecimal(tabDet.Rows[j]["Amt"]);
decimal before = SafeValue.SafeDecimal(tabDet.Rows[j]["Amt"]);
sql = string.Format(@"insert into Hr_PayrollDet(PayrollId,ChgCode,Description,Amt,CreateBy,CreateDateTime,Before) values({0},'{1}','{2}',{3},'{4}',getdate(),{3})", payroll.Id, code, des, payamt, name);
ConnectSql.ExecuteSql(sql);
}
auto_calculate(person, payroll.Id);
InsertLeave(payroll.Id, person, from, to, name);
C2.HrPayroll.UpdateMaster(payroll.Id);
CalcCpf(payroll.Id);
#endregion
result = true;
}
}
}
if (result)
{
value = "Success!";
btn_Sch_Click(null, null);
}
}
e.Result = value;
}
#endregion
}
}
private void Create_Invoice(DevExpress.Web.ASPxGridView.ASPxGridViewCustomDataCallbackEventArgs e, int rowIndex)
{
ASPxLabel lbl_ClientId = this.grid_Transport.FindRowCellTemplateControl(rowIndex, null, "lbl_ClientId") as ASPxLabel;
ASPxLabel lbl_JobNo = this.grid_Transport.FindRowCellTemplateControl(rowIndex, null, "lbl_JobNo") as ASPxLabel;
TextBox txt_cntId = this.grid_Transport.FindRowCellTemplateControl(rowIndex, null, "txt_cntId") as TextBox;
ASPxDateEdit txt_DocDt = this.grid_Transport.FindRowCellTemplateControl(rowIndex, null, "txt_DocDt") as ASPxDateEdit;
string user = HttpContext.Current.User.Identity.Name;
string acCode = EzshipHelper.GetAccArCode("", "SGD");
string sql = string.Format(@"select Vessel,Voyage,Pol,Pod,EtaDate from CTM_Job where JobNo='{0}'", lbl_JobNo.Text);
DataTable dt_job = ConnectSql.GetTab(sql);
DateTime eta = DateTime.Today;
if (dt_job.Rows.Count > 0)
{
eta = SafeValue.SafeDate(dt_job.Rows[0]["EtaDate"], DateTime.Today);
}
string[] ChgCode_List = { "TRUCKING", "FUEL", "DHC", "PORTENT", "CMS", "PSA LOLO", "", "", "", ""
, "WEIGNING", "WASHING", "REPAIR"
, "DETENTION", "DEMURRAGE", "C/S LOLO", "CNL/SHIPMENT"
, "EMF", "OTHER", "", "PSA STORAGE", "EX ONE-WAY", "WRONG WEIGHT",
"ELECTRICITY", "PERMIT", "EXCHANGE DO", "SEAL", "DOCUMENTATION",
"ERP CHARGES", "HEAVYWEIGHT 23/24T", "PSA FLEXIBOOK", "PSA NO SHOW",
"CHASSIS DEMURRAGE", "PARKING", "SHIFTING", "STAND-BY", "MISC 1", "MISC 2", "MISC 3" };
sql = string.Format(@"select * from CTM_JobDet1 where JobNo='{0}'", lbl_JobNo.Text);
DataTable dt = ConnectSql.GetTab(sql);
if (dt.Rows.Count > 0)
{
DateTime dtime = txt_DocDt.Date;
string invN = C2Setup.GetNextNo("", "AR-IV", dtime);
string sql_cnt = string.Format(@"select count(*) from XAArInvoice where PartyTo='{0}' and MastRefNo='{1}'", lbl_ClientId.Text, lbl_JobNo.Text);
int cnt = SafeValue.SafeInt(ConnectSql_mb.ExecuteScalar(sql_cnt), 0);
string des = "";
if (cnt == 0)
{
#region Inv Mast
sql = string.Format(@"insert into XAArInvoice (DocType,DocDate,PartyTo,DocNo,AcYear,AcPeriod,Term,DocDueDate,Description,
CurrencyId,MastType,ExRate,ExportInd,CancelDate,CancelInd,UserId,EntryDate,Eta,AcCode,AcSource,MastRefNo)
values('IV','{5:yyyy-MM-dd}','{4}','{0}','{6}','{7}','CASH','{5:yyyy-MM-dd}','',
'SGD','CTM',1,'N','19000101','N','{1}',getdate(),'17530101','{2}','DB','{3}')
select @@IDENTITY", invN, user, acCode, lbl_JobNo.Text, lbl_ClientId.Text, dtime, dtime.Year, dtime.Month);
string docId = ConnectSql_mb.ExecuteScalar(sql);
C2Setup.SetNextNo("", "AR-IV", invN, dtime);
for (int i = 0; i < dt.Rows.Count; i++)
{
string cntNo = SafeValue.SafeString(dt.Rows[i]["ContainerNo"]);
string cntType = SafeValue.SafeString(dt.Rows[i]["ContainerType"]);
//if()
string sql_part1 = string.Format(@"insert into XAArInvoiceDet (DocId,DocNo,DocType,DocLineNo,AcCode,AcSource,ChgCode,ChgDes1,
GstType,Qty,Price,Unit,Currency,ExRate,Gst,GstAmt,DocAmt,LocAmt,LineLocAmt,MastRefNo,JobRefNo,MastType)
values");
sql = "";
des += cntNo + " / " + cntType + " ";
for (int j = 0, j1 = 0; j < ChgCode_List.Length; j++)
{
string sql_chgCode = string.Format(@"select top 1 ChgcodeId,ChgcodeDes from XXChgCode where ChgcodeDes like '{0}%'", ChgCode_List[j]);
if (ChgCode_List[j].Equals("TRUCKING"))
{
sql_chgCode = string.Format(@"select top 1 ChgcodeId,ChgcodeDes from XXChgCode where ChgcodeDes like '{0}%'", ChgCode_List[j] + " " + cntType.Substring(0, 2));
}
DataTable dt_chgCode = ConnectSql.GetTab(sql_chgCode);
string chgCodeId = "";
string note = "";
if (dt_chgCode.Rows.Count > 0)
{
chgCodeId = SafeValue.SafeString(dt_chgCode.Rows[0]["ChgcodeId"]);
note = SafeValue.SafeString(dt_chgCode.Rows[0]["ChgcodeDes"]);
}
else
{
chgCodeId = ChgCode_List[j];
}
if (!ChgCode_List[j].Equals("") && !ChgCode_List[j].Equals("MISC") && !ChgCode_List[j].Equals("REMARK"))
{
j1++;
decimal temp_fee = SafeValue.SafeDecimal(dt.Rows[i]["Fee" + (j + 1)]);
if (temp_fee != 0)
{
note += dt.Rows[i]["FeeNote" + (j + 1)].ToString();
string sql_part2 = string.Format(@"('{0}','{1}','IV','{2}','31','CR','{3}','{4}','Z',1,{5},'','SGD',1,0,0,{5},{5},{5},'{6}','{7}','{8}')", docId, invN, j1, chgCodeId, note, temp_fee, lbl_JobNo.Text, cntNo, "CTM");
sql += sql.Length > 0 ? "," + sql_part2 : sql_part2;
}
}
}
if (sql.Length > 0)
{
sql = sql_part1 + sql;
int re = ConnectSql.ExecuteSql(sql);
e.Result = invN;
des = "Vessel/Voy:" + dt_job.Rows[0]["Vessel"] + " / " + dt_job.Rows[0]["Voyage"] + "\n" + "Pol/Pod:" + dt_job.Rows[0]["Pol"] + " / " + dt_job.Rows[0]["Pod"] + "\n" + "Eta:" + eta.ToString("dd.MM.yy") + "\n" + "Container No: " + des;
UpdateMaster(SafeValue.SafeInt(docId, 0), des);
}
}
#endregion
}
else
{
string sql_id = string.Format(@"select SequenceId,DocNo from XAArInvoice where PartyTo='{0}'", lbl_ClientId.Text);
DataTable dt_inv = ConnectSql.GetTab(sql_id);
int sequenceId = 0;
if (dt_inv.Rows.Count > 0)
{
sequenceId = SafeValue.SafeInt(dt_inv.Rows[0]["SequenceId"], 0);
invN = SafeValue.SafeString(dt_inv.Rows[0]["DocNo"]);
}
#region Inv Det
for (int i = 0; i < dt.Rows.Count; i++)
{
string cntNo = SafeValue.SafeString(dt.Rows[i]["ContainerNo"]);
string cntType = SafeValue.SafeString(dt.Rows[i]["ContainerType"]);
//if()
string sql_part1 = string.Format(@"insert into XAArInvoiceDet (DocId,DocNo,DocType,DocLineNo,AcCode,AcSource,ChgCode,ChgDes1,
GstType,Qty,Price,Unit,Currency,ExRate,Gst,GstAmt,DocAmt,LocAmt,LineLocAmt,MastRefNo,JobRefNo,MastType)
values");
sql = "";
des += cntNo + " / " + cntType + " ";
for (int j = 0, j1 = 0; j < ChgCode_List.Length; j++)
{
sql_cnt = string.Format(@"select count(*) from XAArInvoiceDet where ChgCode='{0}' and JobRefNo='{1}'", ChgCode_List[j], cntNo);
cnt = SafeValue.SafeInt(ConnectSql_mb.ExecuteScalar(sql_cnt), 0);
if (cnt == 0)
{
string sql_chgCode = string.Format(@"select top 1 ChgcodeId,ChgcodeDes from XXChgCode where ChgcodeDes like '{0}%'", ChgCode_List[j]);
if (ChgCode_List[j].Equals("TRUCKING"))
{
sql_chgCode = string.Format(@"select top 1 ChgcodeId,ChgcodeDes from XXChgCode where ChgcodeDes like '{0}%'", ChgCode_List[j] + " " + cntType.Substring(0, 2));
}
DataTable dt_chgCode = ConnectSql.GetTab(sql_chgCode);
string chgCodeId = "";
string note = "";
if (dt_chgCode.Rows.Count > 0)
{
chgCodeId = SafeValue.SafeString(dt_chgCode.Rows[0]["ChgcodeId"]);
note = SafeValue.SafeString(dt_chgCode.Rows[0]["ChgcodeDes"]);
}
else
{
chgCodeId = ChgCode_List[j];
}
if (!ChgCode_List[j].Equals("") && !ChgCode_List[j].Equals("MISC") && !ChgCode_List[j].Equals("REMARK"))
{
j1++;
decimal temp_fee = SafeValue.SafeDecimal(dt.Rows[i]["Fee" + (j + 1)]);
if (temp_fee != 0)
{
note += dt.Rows[i]["FeeNote" + (j + 1)].ToString();
string sql_part2 = string.Format(@"('{0}','{1}','IV','{2}','','CR','{3}','{4}','Z',1,{5},'','SGD',1,0,0,{5},{5},{5},'{6}','{7}','{8}')", sequenceId, invN, j1, chgCodeId, note, temp_fee, lbl_JobNo.Text, cntNo, "CTM");
sql += sql.Length > 0 ? "," + sql_part2 : sql_part2;
}
}
}
}
if (sql.Length > 0)
{
sql = sql_part1 + sql;
int re = ConnectSql.ExecuteSql(sql);
e.Result = invN;
des = "Vessel/Voy:" + dt_job.Rows[0]["Vessel"] + " / " + dt_job.Rows[0]["Voyage"] + "\n" + "Pol/Pod:" + dt_job.Rows[0]["Pol"] + " / " + dt_job.Rows[0]["Pod"] + "\n" + "Eta:" + eta.ToString("dd.MM.yy") + "\n" + "Container No: " + des;
UpdateMaster(SafeValue.SafeInt(sequenceId, 0), des);
}
}
#endregion
}
}
}
private void auto_calculate(int person, int payrollId)
{
string name = HttpContext.Current.User.Identity.Name;
bool action = false;
decimal salary = 0;
DateTime now = DateTime.Now;
string sql = string.Format(@"select Person,PayItem,Amt,Remark,'NO' as IsCal from Hr_Quote where Person={0}", person);
DataTable dt = ConnectSql_mb.GetDataTable(sql);
for (int i = 0; i < dt.Rows.Count; i++)
{
Wilson.ORMapper.OPathQuery query = new Wilson.ORMapper.OPathQuery(typeof(HrPerson), "Id='" + person + "'");
HrPerson hr = C2.Manager.ORManager.GetObject(query) as HrPerson;
if (hr != null)
{
DateTime birthday = hr.BirthDay;
int age = now.Year - birthday.Year;
if (now.Month < birthday.Month || (now.Month == birthday.Month && now.Day < birthday.Day))
{
age--;
}
DataRow row = dt.Rows[i];
string department = hr.Department;
string payItem = SafeValue.SafeString(row["PayItem"]);
string isCal = SafeValue.SafeString(row["IsCal"]);
string remark = SafeValue.SafeString(row["Remark"]);
string sql_item = string.Format(@"select Description from Hr_PayItem where (Code='{0}' or Description like '%{0}%')", payItem);
string des = SafeValue.SafeString(ConnectSql_mb.ExecuteScalar(sql_item));
if (des.ToLower().Contains("basic"))
{
#region Salary
//if (isCal == "NO")
//{
decimal amt = Get_Incentive(hr.Name);
if (amt > 0)
{
salary = SafeValue.SafeDecimal(row["Amt"]) + amt;
}
else
{
amt = Get_MonthlyVC(person);
salary = SafeValue.SafeDecimal(row["Amt"]) + amt;
}
//}
if (isCal == "YES")
{
DateTime beginDate = txt_from.Date;
DateTime resignDate = txt_end.Date;
DateTime beginDate1 = txt_from.Date;
DateTime resignDate1 = txt_end.Date;
sql = string.Format(@"select top 1 BeginDate,ResignDate from Hr_PersonDet1 where Person={0} and month(ResignDate)={1} order by Id", person, txt_end.Date.Month);
DataTable dt_det1 = ConnectSql_mb.GetDataTable(sql);
if (dt_det1.Rows.Count > 0)
{
for (int j = 0; j < dt_det1.Rows.Count; j++)
{
beginDate = SafeValue.SafeDate(dt_det1.Rows[j]["BeginDate"], now);
resignDate = SafeValue.SafeDate(dt_det1.Rows[j]["ResignDate"], now);
beginDate1 = beginDate.AddDays(1 - beginDate.Day); //这个月的第一天
resignDate1 = resignDate.AddDays(1 - resignDate.Day).AddMonths(1).AddDays(-1); //这个月的最后一天
}
}
else
{
beginDate1 = beginDate.AddDays(1 - beginDate.Day); //这个月的第一天
resignDate1 = resignDate.AddDays(1 - resignDate.Day).AddMonths(1).AddDays(-1); //这个月的最后一天
}
sql = string.Format(@"select WorkDays from Hr_MastData where Type='Department' and Code='{0}'", department);
decimal workDays = SafeValue.SafeDecimal(ConnectSql_mb.ExecuteScalar(sql));
TimeSpan span = resignDate - beginDate;
TimeSpan span1 = resignDate1 - beginDate1;
int days = Get_TotalWorkDays(beginDate, resignDate, workDays) + 1;
int days1 = Get_TotalWorkDays(beginDate1, resignDate1, workDays) + 1;
decimal dayRate = SafeValue.ChinaRound(salary / days1, 2);
salary = SafeValue.ChinaRound(dayRate * days, 2);
sql = string.Format(@"insert into Hr_PayrollDet(PayrollId,ChgCode,Description,Amt,CreateBy,CreateDateTime,Before) values({0},'{1}','{2}',{3},'{4}',getdate(),{3})", payrollId, payItem, remark, salary, name);
ConnectSql.ExecuteSql(sql);
}
action = true;
#endregion
}
if (action)
{
#region CPF
if ((des.ToLower().Contains("cpf employee") || des.ToLower().Contains("employee cpf")) && isCal == "YES")
{
decimal amt = 0;
string sql_rate = string.Format(@"select Rate from Hr_Rate where (Age<{0} and Age1>{0}) and PayItem='{1}'", age, payItem);
DataTable dt_rate = ConnectSql_mb.GetDataTable(sql_rate);
if (dt_rate.Rows.Count > 0)
{
amt = -SafeValue.ChinaRound(salary * (SafeValue.SafeDecimal(dt_rate.Rows[0]["Rate"]) / 100), 2);
}
sql = string.Format(@"insert into Hr_PayrollDet(PayrollId,ChgCode,Description,Amt,CreateBy,CreateDateTime,Before) values({0},'{1}','{2}',{3},'{4}',getdate(),{3})", payrollId, payItem, remark, amt, name);
ConnectSql.ExecuteSql(sql);
}
if ((des.ToLower().Contains("cpf employer") || des.ToLower().Contains("employer cpf")) && isCal == "YES")
{
decimal amt = 0;
string sql_rate = string.Format(@"select Rate from Hr_Rate where (Age<{0} and Age1>{0}) and PayItem='{1}'", age, payItem);
DataTable dt_rate = ConnectSql_mb.GetDataTable(sql_rate);
if (dt_rate.Rows.Count > 0)
{
amt = SafeValue.ChinaRound(salary * (SafeValue.SafeDecimal(dt_rate.Rows[0]["Rate"]) / 100), 2);
}
sql = string.Format(@"insert into Hr_PayrollDet(PayrollId,ChgCode,Description,Amt,CreateBy,CreateDateTime,Before) values({0},'{1}','{2}',{3},'{4}',getdate(),{3})", payrollId, payItem, remark, amt, name);
ConnectSql.ExecuteSql(sql);
}
#endregion
#region Monthy Variable Component
if (payItem == "MonthlyVC" && isCal == "YES")
{
decimal amt = Get_Incentive(hr.Name);
sql = string.Format(@"insert into Hr_PayrollDet(PayrollId,ChgCode,Description,Amt,CreateBy,CreateDateTime,Before) values({0},'{1}','{2}',{3},'{4}',getdate(),{3})", payrollId, payItem, remark, amt, name);
ConnectSql.ExecuteSql(sql);
}
#endregion
#region SDL
if (payItem == "SDL" && isCal == "YES")
{
decimal amt = SafeValue.ChinaRound(salary * SafeValue.SafeDecimal(0.0025), 2);
if (amt > SafeValue.SafeDecimal(11.25))
{
amt = SafeValue.SafeDecimal(11.25);
}
sql = string.Format(@"insert into Hr_PayrollDet(PayrollId,ChgCode,Description,Amt,CreateBy,CreateDateTime,Before) values({0},'{1}','{2}',{3},'{4}',getdate(),{3})", payrollId, payItem, remark, amt, name);
ConnectSql.ExecuteSql(sql);
}
#endregion
}
}
}
}
protected void btn_search_Click(object sender, EventArgs e)
{
string From = txt_search_dateFrom.Date.ToString("yyyyMMdd");
string To = txt_search_dateTo.Date.ToString("yyyyMMdd");
//string ContNo = txt_search_ContNo.Text;
bool ContStauts_N = cb_ContStatus1.Checked;
bool ContStauts_Int = cb_ContStatus2.Checked;
bool ContStauts_C = cb_ContStatus3.Checked;
bool ContStatus_UnC = cb_ContStatus4.Checked;
string JobNo = txt_search_jobNo.Text;
string JobType = search_JobType.Text;
string Vessel = txt_Vessel.Text;
string Client = btn_ClientId.Text;
string hblNo = txt_HblNo.Text.Trim();
//string ClientName = info["ClientName"].ToString();
List <ConnectSql_mb.cmdParameters> list = new List <ConnectSql_mb.cmdParameters>();
list.Add(new ConnectSql_mb.cmdParameters("@DateFrom", From, SqlDbType.NVarChar, 8));
list.Add(new ConnectSql_mb.cmdParameters("@DateTo", To, SqlDbType.NVarChar, 8));
list.Add(new ConnectSql_mb.cmdParameters("@JobNo", JobNo, SqlDbType.NVarChar, 100));
list.Add(new ConnectSql_mb.cmdParameters("@JobType", JobType, SqlDbType.NVarChar, 10));
list.Add(new ConnectSql_mb.cmdParameters("@Vessel", Vessel, SqlDbType.NVarChar, 100));
list.Add(new ConnectSql_mb.cmdParameters("@Client", Client, SqlDbType.NVarChar, 100));
list.Add(new ConnectSql_mb.cmdParameters("@HblNo", hblNo, SqlDbType.NVarChar, 100));
string sql = string.Format(@"select job.Id,job.JobNo,job.StatusCode as JobStatus,job.JobDate,job.ClientRefNo,
job.PermitNo,job.Remark,job.SpecialInstruction,job.EtaDate,job.EtaTime,job.EtdDate,job.OperatorCode,job.CarrierBkgNo,det2.Statuscode,
job.Pol,job.Pod,job.Vessel,job.Voyage,job.PickupFrom,job.DeliveryTo,h.HblNo,WarehouseCode,
(select top 1 Code from XXParty where PartyId=job.ClientId) as client,(select top 1 code from XXParty where PartyId=job.HaulierId) as Haulier,
job.Terminalcode,job.JobType
from CTM_Job as job left join CTM_JobDet2 as det2 on det2.JobNo=job.JobNo left join (select top 1 * from job_house) h on h.JobNo=job.JobNo");
string sql_where = "";
if (hblNo.Length > 0)
{
sql_where = GetWhere(sql_where, "h.HblNo=@HblNo");
}
if (JobNo.Length > 0)
{
sql_where = GetWhere(sql_where, "job.JobNo=@JobNo");
}
if (sql_where.Equals(""))
{
//sql_where = " datediff(d,@DateFrom,det1.ScheduleDate)>=0 and datediff(d,@DateTo,det1.ScheduleDate)<=0";
if (Vessel.Length > 0)
{
sql_where = GetWhere(sql_where, "job.Vessel=@Vessel");
}
if (JobType.Length > 0 && !JobType.Equals("ALL"))
{
sql_where = GetWhere(sql_where, "job.JobType=@JobType");
}
if (Client.Length > 0)
{
sql_where = GetWhere(sql_where, "job.clientId=@Client");
}
if (sql_where.Length > 0)
{
sql_where = "and " + sql_where;
}
sql += " where job.JobType in ('WGR','WDO','TPT') " + sql_where;
}
else
{
sql += " where " + sql_where;
sql += " order by job.EtaDate,job.JobNo desc, job.JobDate asc";
}
DataTable dt = ConnectSql_mb.GetDataTable(sql, list);
//throw new Exception(sql.ToString());
this.grid_Transport.DataSource = dt;
this.grid_Transport.DataBind();
}
private int Get_TotalWorkDays(DateTime date1, DateTime date2, decimal workDays)
{
int days = 0;
TimeSpan span = date2 - date1;
DateTime now = DateTime.Now;
DateTime date3 = DateTime.Today; //公休起始时间
DateTime date4 = DateTime.Today; //公休结束时间
string sql = string.Format(@"select * from holidays where FromMonth={0}", date1.Month);
DataTable tab = ConnectSql_mb.GetDataTable(sql);
for (int i = 0; i < tab.Rows.Count; i++)
{
int year = now.Year;
string fromMonth = SafeValue.SafeString(tab.Rows[i]["FromMonth"]);
if (fromMonth.Length == 1)
{
fromMonth = "0" + fromMonth;
}
string fromDay = SafeValue.SafeString(tab.Rows[i]["FromDay"]);
if (fromDay.Length == 1)
{
fromDay = "0" + fromDay;
}
string toMonth = SafeValue.SafeString(tab.Rows[i]["ToMonth"]);
if (toMonth.Length == 1)
{
toMonth = "0" + toMonth;
}
string toDay = SafeValue.SafeString(tab.Rows[i]["ToDay"]);
if (toDay.Length == 1)
{
toDay = "0" + toDay;
}
string strFrom = SafeValue.SafeString(year + fromMonth + fromDay);
string strTo = SafeValue.SafeString(year + toMonth + toDay);
date3 = new DateTime(SafeValue.SafeInt(year, 0), SafeValue.SafeInt(fromMonth, 0), SafeValue.SafeInt(fromDay, 0));
date4 = new DateTime(SafeValue.SafeInt(year, 0), SafeValue.SafeInt(toMonth, 0), SafeValue.SafeInt(toDay, 0));
}
int intDiffer = span.Days; //相差天数的int值
for (int i = 0; i < intDiffer; i++) //从dt1开始一天天加，判断临时的日期值是不是星期六或星期天，如果既不是星期六，也不是星期天，而且也不在dt3和dt4之间，则该天为工作日，intReturn加1
{
DateTime dtTemp = date1.Date.AddDays(i);
if (workDays == 5)
{
if ((dtTemp.DayOfWeek != System.DayOfWeek.Sunday) && (dtTemp.DayOfWeek != System.DayOfWeek.Saturday))
{
days++;
// if ((dtTemp.Date < date3.Date) || (dtTemp.Date > date4.Date))
// {
// days++;
// }
}
}
else
{
if ((dtTemp.DayOfWeek != System.DayOfWeek.Sunday))
{
//if ((dtTemp.Date < date3.Date) || (dtTemp.Date > date4.Date))
//{
days++;
//}
}
}
}
return(days);
}
